Menu
I have a week number and a year, and would like to calculate the date of the Monday for that specific week in Microsoft Excel.
Year Week Date (Monday) 2012 1 January 2, 2012 2013 16 April 15, 2013 2014 42 October 13, 2014 What formula can I use to convert a calendar week to a specific date?
314
Please do as follows: In a blank cell, please enter the formula =CHOOSE(WEEKDAY(B1),"Sun","Mon","Tue","Wed","Thu","Fri","Sat"), and press the Enter key. This formula will convert the date to the day of week as below screenshot shown.
If you just need to get the month as Arabic number, you can use this formula =MONTH(DATE(A2,1,B2*7-2)-WEEKDAY(DATE(B2,1,3))). Tip: In the above formulas, A2 indicates the year cell, B2 is the week number cell.
For ISO week numbers you can use this formula to get the Monday
=DATE(A2,1,-2)-WEEKDAY(DATE(A2,1,3))+B2*7
assuming year in A2 and week number in B2
it's the same as my answer here https://stackoverflow.com/a/10855872/1124287
132
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
