SOLVED
Home

Week Numbers between a Start and End Date

%3CLINGO-SUB%20id%3D%22lingo-sub-186768%22%20slang%3D%22en-US%22%3EWeek%20Numbers%20between%20a%20Start%20and%20End%20Date%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-186768%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20All%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20dont%20know%20if%20anyone%20can%20help!!%20a%20concept%20that%20sounds%20simple%20in%20my%20head%20is%20just%20not%20coming%20into%20fruition%20when%20im%20trying%20to%20do%20it%20in%20Excel.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20am%20trying%20to%20find%20a%20formula%20that%20will%20calculate%20the%20week%20number%20of%20a%20certain%20date%20that%20will%20fall%20between%20a%20start%20and%20date.%20So%20for%20example%20if%20my%20start%20date%20is%2025%2F04%2F2018%20and%20an%20end%20date%20of%26nbsp%3B20%2F07%2F2018%20this%20will%20be%2013%20weeks%20(Monday%20to%20Friday)%26nbsp%3B%20%2C%20but%20if%20i%20want%20to%20know%20what%20week%20number%20the%2013th%20June%20would%20fall%20on%20is%20there%20formula%20that%20can%20do%20this%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESo%20far%20i%20have%20managed%20to%20use%20a%20formula%20where%20i%20physically%20just%20need%20to%20look%20where%20the%20start%20date%20falls%20between%20a%20Week%20start%20and%20week%20end%20by%20using%20the%20roll%20back%20to%26nbsp%3Bthe%20previous%26nbsp%3BMonday%26nbsp%3Bfrom%20the%20start%20date%20but%20the%20snag%20is%20when%20I%20enter%20a%20date%20that%20falls%20on%20a%20Monday%20i%20do%20not%20want%20it%20to%20roll%20back%20to%20the%20previous%20monday%20as%20this%20Monday%20would%20count%20as%20week%20one%20%3F%3F%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Eif%20i%20am%20going%20about%20this%20all%20wrong%20or%20if%20there's%20an%20easier%20way%20i%20would%20be%20grateful%20for%20some%20advice%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-186768%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20%26amp%3B%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3Eweek%20numbers%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-186816%22%20slang%3D%22en-US%22%3ERe%3A%20Week%20Numbers%20between%20a%20Start%20and%20End%20Date%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-186816%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20Susan%2C%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIf%20you'd%20like%20to%20calculate%20number%20of%20full%20weeks%20from%20Start%20Date%20that%20could%20be%3C%2FP%3E%0A%3CPRE%3E%3DINT((%26lt%3Byour%20date%26gt%3B%20-%20%26lt%3BStart%20date%26gt%3B)%2F7)%3C%2FPRE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-186772%22%20slang%3D%22en-US%22%3ERe%3A%20Week%20Numbers%20between%20a%20Start%20and%20End%20Date%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-186772%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20Jamil%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Ethank%20your%20for%20your%20response%2C%20will%20this%20give%20me%20the%20week%20number%20which%20relates%20to%20the%20year%20i.e%20Jan%20to%20Dec%3F%26nbsp%3B%3C%2FP%3E%3CP%3EI%20am%20looking%20for%20i.e%20Start%20Date%2018%2F12%2F17%26nbsp%3B%20End%20Date%2009%2F03%2F2018%26nbsp%3B%20%26nbsp%3B%20but%2020%2F02%2F2018%20would%20be%20week%2011%20%3F%26nbsp%3B%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-186771%22%20slang%3D%22en-US%22%3ERe%3A%20Week%20Numbers%20between%20a%20Start%20and%20End%20Date%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-186771%22%20slang%3D%22en-US%22%3E%3CP%3EOr%3C%2FP%3E%0A%3CPRE%3E%3DISOWEEKNUM(A1)%3C%2FPRE%3E%0A%3CP%3Eif%20you%20are%20in%20this%20system%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-186769%22%20slang%3D%22en-US%22%3ERe%3A%20Week%20Numbers%20between%20a%20Start%20and%20End%20Date%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-186769%22%20slang%3D%22en-US%22%3EIf%20you%20want%20to%20return%20the%20week%20number%20of%20a%20particular%20date%20then%20use%20function%20of%20WEEKNUM%3CBR%20%2F%3EA1%20having%20the%20date%20use%20the%20formula%20below%3CBR%20%2F%3E%3DWEEKNUM(A1)%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-763634%22%20slang%3D%22en-US%22%3ERe%3A%20Week%20Numbers%20between%20a%20Start%20and%20End%20Date%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-763634%22%20slang%3D%22en-US%22%3E%3CP%3EHi%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI've%20same%20problem%20as%20Susan%20mentioned%2C%20Let's%20take%20July%20month%20and%20the%20Employee%20started%20working%20on%20Jul1%20(monday)%20and%20his%20ending%20date%20is%206(saturday)%20i%20would%20get%201%20as%20output.%20If%20start%20date%20is%20Jul1%20(monday)%20and%20end%20date%20is%20July%2010%20(wednesday)%20then%20i%20should%20get%20output%20as%202.%3C%2FP%3E%3CP%3EAnother%20question%3A%20irrespective%20of%20start%20date(i.e%2C%26nbsp%3B%20july%201%20-%20July%206)%20if%20end%20date%20falls%20before%20tue%20(i.e%2C%20july%209)%20i%20should%20get%20output%20as%201%20and%20so%20on%20for%20the%20following%20days.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Eappreciate%20your%20help%20in%20finding%20the%20solution%2C%20thanks%20in%20advance.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EVaman21%3C%2FP%3E%3CBLOCKQUOTE%3E%3CHR%20%2F%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3Bwrote%3A%3CBR%20%2F%3E%3CP%3EHi%20Susan%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20you'd%20like%20to%20calculate%20number%20of%20full%20weeks%20from%20Start%20Date%20that%20could%20be%3C%2FP%3E%3CPRE%3E%3DINT((%26lt%3Byour%20date%26gt%3B%20-%20%26lt%3BStart%20date%26gt%3B)%2F7)%3C%2FPRE%3E%3CHR%20%2F%3E%3C%2FBLOCKQUOTE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-763686%22%20slang%3D%22en-US%22%3ERe%3A%20Week%20Numbers%20between%20a%20Start%20and%20End%20Date%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-763686%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F141112%22%20target%3D%22_blank%22%3E%40Susan%20Authers%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHere%20is%20a%20complete%20guide%20to%20date%20math%20(Including%20the%20Weeknumber%20function)%20%2B%20a%20Free%20quick%20reference%20card%20to%20date%20Math%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Fwww.youtube.com%2Fwatch%3Fv%3DqIZxeOq-QDk%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fwww.youtube.com%2Fwatch%3Fv%3DqIZxeOq-QDk%3C%2FA%3E%3C%2FP%3E%3CP%3EHope%20that%20helps%3C%2FP%3E%3CP%3ENabil%20Mourad%3C%2FP%3E%3C%2FLINGO-BODY%3E
Susan Authers
New Contributor

Hi All

 

I dont know if anyone can help!! a concept that sounds simple in my head is just not coming into fruition when im trying to do it in Excel.

 

I am trying to find a formula that will calculate the week number of a certain date that will fall between a start and date. So for example if my start date is 25/04/2018 and an end date of 20/07/2018 this will be 13 weeks (Monday to Friday)  , but if i want to know what week number the 13th June would fall on is there formula that can do this?

 

So far i have managed to use a formula where i physically just need to look where the start date falls between a Week start and week end by using the roll back to the previous Monday from the start date but the snag is when I enter a date that falls on a Monday i do not want it to roll back to the previous monday as this Monday would count as week one ?? 

 

if i am going about this all wrong or if there's an easier way i would be grateful for some advice 

6 Replies
If you want to return the week number of a particular date then use function of WEEKNUM
A1 having the date use the formula below
=WEEKNUM(A1)

Or

=ISOWEEKNUM(A1)

if you are in this system

Hi Jamil 

 

thank your for your response, will this give me the week number which relates to the year i.e Jan to Dec? 

I am looking for i.e Start Date 18/12/17  End Date 09/03/2018    but 20/02/2018 would be week 11 ?  

Solution

Hi Susan,

 

If you'd like to calculate number of full weeks from Start Date that could be

=INT((<your date> - <Start date>)/7)

Hi@Sergei Baklan 

 

I've same problem as Susan mentioned, Let's take July month and the Employee started working on Jul1 (monday) and his ending date is 6(saturday) i would get 1 as output. If start date is Jul1 (monday) and end date is July 10 (wednesday) then i should get output as 2.

Another question: irrespective of start date(i.e,  july 1 - July 6) if end date falls before tue (i.e, july 9) i should get output as 1 and so on for the following days.

 

appreciate your help in finding the solution, thanks in advance.

 

Vaman21


@Sergei Baklan wrote:

Hi Susan,

 

If you'd like to calculate number of full weeks from Start Date that could be

=INT((<your date> - <Start date>)/7)

 

@Susan Authers 

Here is a complete guide to date math (Including the Weeknumber function) + a Free quick reference card to date Math

https://www.youtube.com/watch?v=qIZxeOq-QDk

Hope that helps

Nabil Mourad

This tutorial is a Festival of Date Formulas & Functions! With over 40 examples that will allow you to hold a firm grasp of Date Math. I also included on a separate sheet a full definition of each function with the arguments required to use them. You can Download the Exercise file and Follow along
Related Conversations