Menu
Given a year and calendar week, how can I get the tuesday of that week as a date?
923
Select a blank cell you will return the week number, enter this formula: =WEEKNUM(B1,1), and press the Enter key. See screenshot: Notes: (1) In above formula, B1 contains the date that you want to use.
I'm assuming you have week number as a separate field in your table. 1)So firstly, convert your week number field into a dimension. (If its a dimension already, ignore this step.) 2)Then right click on it and change data type to 'Date'.
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.
To convert the week number to date in Excel, you can use the MIN function and the MAX function with the same formula. We saw in the previous formulas, they return Monday or Sunday of week 1 even if it falls within the same year that you give or the earlier year. The start date formula always returns January 1 as the start date of week 1 .
DATE(A2, 1, -2) - WEEKDAY(DATE(A2, 1, 3)) - calculates the date of the last Monday in the previous year. B2 * 7 - adds the number of weeks multiplied by 7 (the number of days in a week) to get the Monday (start date) of the week in question. In the ISO week numbering system,...
WEEKNUM takes two arguments: a date, and (optionally) return_type, which controls the scheme used to calculate the week number. By default, the WEEKNUM function uses a scheme where week 1 begins on January 1, and week 2 begins on the next Sunday (when the return_type argument is omitted, or supplied as 1).
Obviously, you can wrap the Start date formula in the Excel MONTH function to get a month corresponding to the week number. =MONTH (DATE (A2, 1, -2) - WEEKDAY (DATE (A2, 1, 3)) + B2 * 7)
In MySQL the STR_TO_DATE() function can do the trick in just one line!
Example: We want to get the date of the Tuesday of the 32th week of the year 2013.
SELECT STR_TO_DATE('2013 32 Tuesday', '%X %V %W'); would output:
'2013-08-13' I think this is the best and shortest solution to your problem.
149
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
