Excel : Weeknumber within month with criteria

0
votes

I'm trying to make a formule to calculate the weeknumber within a week but with certain criteria. The week always starts on a monday. But if the thursday of that week is in the next month, then that weeknumber is part of the next month.

enter image description here[enter image description here][2]

Right now I have the following formula:

=INTEGER((6+DAY(A4+1-WEEKDAY(A4-1)))/7) where A4 is the date.

This already calculates the weeknumber within the month, but not with the criteria of thursday. An extra added criteria is that the weeknumber should start from 6am on monday instead of at midnight. But that I can solve with an extra column to check for that.

Thanks in advance!

3
excelexcel-formulaweek-number
Please provide a screenshot or a data sample with a scenario that meets the criteria of your question. Put in the desired result manually. Now, as a new user here, please make sure you understand this: Edit your question to provide the details asked for. Don't use comments for that. After you have edited your question , post a comment and use @teylyn to alert me to your changes. – teylyn
@teylyn I have added a screenshot of an Excel sample. Thank you. – Innerv1sion
I think EOMONTH function could help you. Unfortunately I can't help you more atm. – Egan Wolf

3 Answers

1
votes

Consider working out the week number from the previous week number:

  • If it is Monday then
    • If following Thursday goes into the next month, reset to 1
    • Else increase by 1
  • Else use same value

So starting with a 1 in E4

=IF(WEEKDAY(A5)=2,IF(MONTH(A5+3)>MONTH(A5-4),1,E4+1),E4)

entered in E5 and copied down.

enter image description here

EDIT

Above works for 2017 but would need slight change to work for 2018 and onwards because month decreases from 12 to 1 across year boundary:

=IF(WEEKDAY(A5)=2,IF(MONTH(A5+3)<>MONTH(A5-4),1,E4+1),E4)
0
votes

I cant upload a workbook but here is a solution:

Row 2 is headers Row 3 is empty B4 going down is the date(01/01/2017 up to 31/12/2017)

C4 =WEEKDAY(B4,2)

D4 =VLOOKUP($C4,$L$3:$M$9,2,0)

E4 =MONTH(B4)

F4 to J4 are empty

F5 =IF(C5=1,F4+1,F4)

G5 =IF(C5=4,11,0)

H5 =IF(MAX(E5:E11)-MIN(E5:E11)<>0,22,0)

I5 =IF(AND(C5=4,SUM(G5:G11)+SUM(H5:H11)>22),I4+1,0)

J5 =IF(H6-H5<0,1,IF(H6=22,J5,IF(OR(C6=1,I6<>0),J5+1,J5)))

L3 to M9 are vlookups

1   Monday
2   Tuesday
3   Wednesday
4   Thursday
5   Friday
6   Saturday
7   Sunday

Does that work ok for you?

0
votes

In my experience most of those week number questions can be answered with some variation of your original formula - in this case this version should cater for Monday week start with the Thursday determining the month

=INT((6+DAY(B4+4-WEEKDAY(B4-1)))/7)

That works for a date in A4 but assuming you have a date/time in A4 and the week doesn't start until 06:00 on Monday then you can tweak that as follows:

=INT((6+DAY(B4+3.75-WEEKDAY(B4-1.25)))/7)

enter image description here