Determine the month of a week based on in which month a certain weekday is?

0
votes

I want the user to enter year into A1.

I want the user to enter week number into A2.

I want the user to enter weekday (Monday, Tuesday, Wednesday, Thursday, Friday, Saturday or Sunday) in A3.

Then I want A4 to calculate the month of the entered week number where weeks are counted as belonging to a certain month depending on if the specified weekday is in that month or not.

What formula would work in A4? (if you can not solve this problem, but can give me a formula that works if specified weekday can only be thursday, then I welcome that as well.

1
excelexcel-formula
Have you tried anything, yet? - AnyOneElse
I have tried something like =CHOOSE(MONTH(DATE(YEAR;1;WEEK*7-N)-WEEKDAY(DATE(WEEK;1;3)));"Jan";"Feb";"Mar";"Apr";"Maj";"Jun";"Jul";"Aug";"Sep";"Okt";"Nov";"Dec") and varying N in that formula between 1 and 7, (Mon and Sun), but haven't gotten it to work. I don't exactly understand the formula. - user1283776
My formula was inspired from here: extendoffice.com/documents/excel/… - user1283776
do I unsterstand you correctly: you do want to use Excel not VBA? - AnyOneElse
How are you computing the weeknumber? There are various methods used; the most common is the ISO standard (week 1 is the first week of the year containing a Thursday, and the first day of the week is Monday), but there are others that your scheme might be using, and it will make a difference in the computation. - Ron Rosenfeld

1 Answers

0
votes

My idea would be something like that:

(weeknumber-1)*7 + Weekday -> Mon = 1, Tue = 2.... Check the Day of Year [0..365 or 366] and compare to an offset for Jan / Feb / March... depending on whether year has a 29th of Feb - which can be calculated using the year.

Now all you have to do is convert that to code.

EDIT: After I read that page you reffered to, I assume you have trouble understanding Excels Date Syntax http://office.microsoft.com/en-us/excel-help/date-function-HP010062283.aspx

So lets break that code down: What happens here? 

=CHOOSE(MONTH(DATE(A2,1,B2*7-2)-WEEKDAY(DATE(B2,1,3))),"January", "February", "March", "April", "May", "June", "July", "August", "September", "October", "November", "December")

So lets pick that first DATE Statement: Date (as stated in Microsoft's Doc) works as follows: If your month has more than 30/31 days the leftover days get dragged into the next month. So as A1 is the year (yyyy/mm/dd) B2 is the day times 7 == the weeknumber minus some offset BECAUSE the first week shall not be counted (like it is week 1 day 3 but the formula would make it day 10 otherwise)

** https://support.office.com/en-US/Article/WEEKDAY-function-a9784177-5c31-4deb-bc9e-d4ab914983ca?ui=en-US&rs=en-US&ad=US

** same with Month https://support.office.com/en-US/Article/MONTH-function-EEDAC31A-E28C-46FB-B81F-CADB4BC03751?ui=en-US&rs=en-US&ad=US

So what is it with the monthnames? Well, this is just a sinmple enumeration and the CHOOSE function in the very beginning will convert a number between 0...x into the value at the corresponding place in the enum, which is why the months have to be in order.