Formula for calculating week of the month, but using first 7 days as week 1, next 7 days as week 2, etc...

[seaottr]

Hi all,


I've found the formula to calculate the week of a month, but I'm looking for a formula that calculates days 1-7 of the month as Week 1, days 8-14 as Week 2, etc.


Is anyone able to help please?


Thank you in advance!

 

[jmacleary]

Hi there. If your date is in K6 for instance, use this formula: =INT(DAY(K6)/7)+1

 

[seaottr]

Unfortunately that doesn't work. It's giving February 28, 2019 as week 5 (should be week 4), March 7, 2019 as week 2 (should be week 1)

 

[jmacleary]

OK sorry me too hasty. This should do it: =INT((INT(DAY(K6)-1)/7)+1)

 

[seaottr]

OK sorry me too hasty. This should do it: =INT((INT(DAY(K6)-1)/7)+1)

Great! Thank you so much! Works like a charm!

 

[jmacleary]

You're welcome and thanks for the feedback and votes - it makes a difference when you know its been of use!