0

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!

Innerv1sion
  • 5
  • 1
  • 3
  • 1
    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 Aug 24 '17 at 08:20
  • @teylyn I have added a screenshot of an Excel sample. Thank you. – Innerv1sion Aug 24 '17 at 08:50
  • I think `EOMONTH` function could help you. Unfortunately I can't help you more atm. – Egan Wolf Aug 24 '17 at 08:59

3 Answers3

1

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)
Tom Sharpe
  • 30,727
  • 5
  • 24
  • 37
  • I had come to a similar conclusion @TomSharpe, but I never got to the stage if the date started in the middle of the month. The OPs example data does not cover this case and the OP did leave the first date as blank which I am assuming should really be 4. Good answer though and gives exactly the output the OP asked for. – Forward Ed Aug 25 '17 at 17:19
  • Thanks @Forward Ed, it just seems easier this way. It feels as if you'd have to be working backwards otherwise. But to be honest I haven't worked out how OP's original formula got so close to a solution, maybe I should do that next. – Tom Sharpe Aug 25 '17 at 17:40
  • I did eventually come up with a solution for working the week number out from a date. The idea was to define an 'accounting month' for each calendar month e.g. for January 2017 it would go from Monday 2nd to Sunday 29th, then it's not too difficult to get the week number but it did need several helper columns. – Tom Sharpe Aug 29 '17 at 10:14
  • I thought it would be a little more difficult, but I always enjoy a good challenge. – Forward Ed Aug 29 '17 at 11:25
  • Thank you very much for this simple solution. In the meantime I was able to solve it using various helper columns, but this still wasn't foolproof. When I changed the year to 2018 there were some wrong values. The original formula was based on the formule posted on the opening post. But changed it on trial and error to: =INTEGER((1+DAY(B4+3-WEEKDAY(B4-1)))/7)+1 – Innerv1sion Aug 29 '17 at 21:24
  • No problem. My formula works for 2017 but would need slight change to work for 2018 and onwards - will add to post. – Tom Sharpe Aug 31 '17 at 07:45
0

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?

Tejkaran Samra
  • 96
  • 1
  • 14
0

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

barry houdini
  • 45,615
  • 8
  • 63
  • 81