Calculate Week of The Month with Straddling Weeks - Excel

0
votes

I am trying to calculate the weeks of the month, 1-4 or 1-5, where the calendar week that contains the first calendar day of the month is always week 1. So for example, February 1, 2018 falls on a Thursday so the dates Monday January 29 - Sunday February 4 would be week one of February.

I was using the formula =INT((DAY(D2)+6)/7) - which seemed to work until the week splits and crosses the calendar month. It counted the days the cross over from January as week 5 rather than week 1 of the next month.

Data Revised

1
excelweek-number
could you redo the photo with a white background it is hard for these old eyes to read. – Scott Craner
Your current formula does not roll on monday to week 2. by your rules would not 2/5/2018 be the start of week 2. your formula returns 1. – Scott Craner
Thank you Scott. Pretty new to this so I can see where my formula doesn't work because 01/29/2018 returns a 5 but I'm trying to determine how to make it see that 02/01/2018 falls into the week and so from the previous Monday +7 is week 1. Does that make sense? – Beth

1 Answers

1
votes

Try this and see if it works:

=IF(MONTH(A2)=MONTH(A2+7-WEEKDAY(A2,2)),CEILING(((DAY(A2)+WEEKDAY(A2-DAY(A2)-1)))/7,1),1)-AND((WEEKDAY(A2-DAY(A2))=1),MONTH(A2)=MONTH(A2+7-WEEKDAY(A2,2)))

  • Is the following Sunday in the same month as the Date (in A2)

    • If False, Week = 1

    • If True

      • use last Sunday of preceding month as base for week count.
      • Adjust if month starts on a Monday and we are in the first week (there's possibly a better way to do this adjustment)

enter image description here