[SOLVED] Return month for weeknumber

[Hawken]

Hi. I have a sheet for registering work hours on projects. It is organized in months and weeks, and I want to be able to return the correct month for each weeknumber.

The rules are:

A week starts monday and ends sunday.
If a week has days in two months, it should belong to the month with 3 or more workdays (monday - friday) in it.

ex. week 18 in 2013 should return May.

How can i do this?

cheers

Hawken

[martindwilson]

when is week 1?

[XOR LX]

Hi Hawken,

Assuming that Week 1 commences on 1st January 2013 and that the Week Numbers are in column A with Week 1 in cell A2, then enter this formula in B2 and copy down as required:

=TEXT(DATE(2013,MODE(IF((WEEKDAY(DATE(2013,1,1)+(7*(ROW()-2)+(ROW(INDIRECT("1:7"))-1)),2)<=5),MONTH(DATE(2013,1,1)+(7*(ROW()-2)+(ROW(INDIRECT("1:7"))-1))))),1),"mmm")

NOTE: This is an array formula and so needs to be confirmed with CTRL+SHIFT+ENTER, not just enter.

Regards

[Hawken]

Oh, thats a rule aswell. Here in Norway, week 1 is the first week of the year containg atleast 4 days, so if the 1. january is on a friday, week 1 starts on january 4th. This means that whatever week containing the 4th of january is week 1.

[daddylonglegs]

Try this formula for the Month assuming week number in A2 and year in B2

=TEXT(DATE(B2,1,A2*7+1)-WEEKDAY(DATE(B2,1,3)),"mmmm")

The formula calculates the start date (a Monday) of the week in question then adds 3 days to get the Thursday of that week - given your stipulation about weeks which cross months the Thursday will always be within the correct month....so TEXT function just gives you the month of that date

[Hawken]

Seems to be working perfectly. Thanx alot.

edit: Not entirely.

The rule to determine the first week of the year is good with the "thursday rule". But after that I need to use a wednesday rule (week belongs to month with three working days (Mon-fri). Wich parameter do i have to change in your formula?

Hawken

[daddylonglegs]

The rule to determine the first week of the year is good with the "thursday rule". But after that I need to use a wednesday rule (week belongs to month with three working days (Mon-fri). Wich parameter do i have to change in your formula?

Good point - I didn't take on board the "workdays" aspect. To get the Wednesday instead of Thursday you can simply remove the "+1" from the formula, but in the odd case where you want week 1 in a year when week 1 starts on 29th December (e.g. 2015) that would give you December rather than January so you can add in a MAX function to make it work in all cases - revised formula becomes

=TEXT(DATE(B2,1,MAX(1,A2*7-WEEKDAY(DATE(B2,1,3)))),"mmmm")

[Hawken]

Fantastic. Thanx alot.

Hawken