CODEWITHSUNDEEP
excelexcel-formula
user1283776
user1283776

Reputation: 21814

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

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.

Upvotes: 0

Views: 667

Answers (1)

AnyOneElse
AnyOneElse

Reputation: 406

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.

Upvotes: 0

Related Questions