CODEWITHSUNDEEP
excelvbaexcel-formula
Flaw98
Flaw98

Reputation: 84

How to determine what week a day belongs to

I'm having trouble categorizing which week of the month a day belongs to. So, I'm counting a week as first Sunday that passed, even if that week has one or two days, it will be counted as a week. I've attached a photo in order to be more clear in my explanation:

enter image description here

Until now, I have used:

Week 1: 
=IF(AND(DAY(A2)>=1;DAY(A2)<=7);(TIME(HOUR(B2);MINUTE(B2);0)-TIME(HOUR(A2);MINUTE(A2);0))*24;0)

Week 2:
=IF(AND(DAY(A2)>=8;DAY(A2)<=14);(TIME(HOUR(B2);MINUTE(B2);0)-TIME(HOUR(A2);MINUTE(A2);0))*24;0)

Week 3:
=IF(AND(DAY(A2)>=15;DAY(A2)<=21);(TIME(HOUR(B2);MINUTE(B2);0)-TIME(HOUR(A2);MINUTE(A2);0))*24;0)

Week 4:
=IF(AND(DAY(A2)>=22;DAY(A2)<=28);(TIME(HOUR(B2);MINUTE(B2);0)-TIME(HOUR(A2);MINUTE(A2);0))*24;0)

And also Week 5 if the month exceeds 28 days or so:
=IF(DAY(A2)>28;(TIME(HOUR(B2);MINUTE(B2);0)-TIME(HOUR(A2);MINUTE(A2);0))*24;0)

For example in May:

Week 1: May 1-5 not May 1-7, because the 6th and 7th day of May would be Week 2, according to my wrong formula... Week 2: May 6-12 Week 3: May 13-19 Week 4: May 20-26 Week 5: May 27-31

Upvotes: 0

Views: 220

Answers (1)

zipa
zipa

Reputation: 27869

Generally, to get week of month you can use:

=WEEKNUM(A1,2)-WEEKNUM(DATE(YEAR(A1),MONTH(A1),1),2)+1

The 2 in WEEKNUM() refers to Monday as first day of the week, but you can change that.

Upvotes: 6

Related Questions