How to combine day, week, year into a single date

I have a large dataset with three date columns: year (e.g., 2020) month (e.g., 8 for August) and week (1,2,3 or 4). I'm struggling to find a way to combine them into one date in Excel. I can only find options to combine other forms of date e.g., year, month, day.

• This is ambiguous: does your week 1 start on the 1st day of the month, or on the 1st monday of the month, or something else...? Oct 14, 2022 at 12:10
• Each month is split into four weeks and numbered 1,2,3 or 4. Week 1 begins on the first day of the year, week four ends on the last day of the month. It's weekly data from a commodity price index which reports weekly prices in this way. Of course with months being different lengths, in reality these four weeks will vary in length. So it's different from going by the 52 weeks of the year, which are precisely 7 days long. It's sort of a proxy for a week. Oct 14, 2022 at 12:16
• So the dates I have are e.g. 2020/month 8/week 1; 2021/month 3/week 3; 2021/month 5/week 4 meaning the last (rough) week of May 2021; etc. Oct 14, 2022 at 12:18
• Please clarify your specific problem or provide additional details to highlight exactly what you need. As it's currently written, it's hard to tell exactly what you're asking.
– Community Bot
Oct 14, 2022 at 12:38
• In a month with 31 days. Does weeks 1 - 3 have 8 days and week 4 have 7, or week 1 have 7 days and the rest have 8 or some other combination? Oct 14, 2022 at 12:58

2 Answers

If all you need to do with these dates is sort and compare for greater/less/equal, you can adapt Mobus's suggestion. Zero-fill month and week and concatenate year, month, and week:

`=CONCATENATE(TEXT(year,"0000"), TEXT(month,"00"), TEXT(week,"00"))`

would give 20200801 as a text value for the date in your example. You can sort these in a meaningful way and compare them for equal, less, or greater. If you need a numeric value, you can wrap the whole thing in Excel's `value` function to turn the text back into a number.

What you can't do is arithmetic on those numbers. You can't for example, subtract 30 to get a value representing a date 30 days earlier.

You've said that week 1 starts on the first day of a month and week 4 ends on the last day, so August's weeks would be 7.75 days long. More likely you'd have three eight-day weeks and a seven-day week. You can pinpoint the first day. If you assume that week 4 is seven days, you can compute the start of week 4. You could then do some convoluted interpolations to try to figure out the dates of weeks two and three (and you can hit them exactly in February of non-leap years), but any arithmetic would have a resolution of about seven days. So, I hope you don't need to do arithmetic. If you do, please ask and I'll look at it.

To construct a date in Excel date serial number format use e.g.

``````=DATE(2022, 8, 1)
``````

Which will return 2022/08/01 (depending on your default date format). If you change the format to "long date" you'll get "Monday, 01 August 2022". If you change it simply to a number format, you'll get 44774 (i.e. the number of days since 0 January 1900).

To get a single date, you need to turn the week number into a day number. Easiest is simply to substract 1 and times 7, so week 1 will always refer to the 1st, week 2 to the 8th, week 3 to the 15th and week 4 to the 22nd. This also means week 4 is between 7 and 10 days long.

``````=DATE(2022, 8, (A1-1)*7 + 1)
``````

Using the date serial number format means that you can apply mathematical formulae to the dates (e.g. subtract two dates to get the difference in days, or jump a year `=EDATE(A4, 12)`, or extract the day of the week `=WEEKDAY(A4)`.

As an alternative, if you simply need all the values in a single column, just treat the data as text and concatenate it with the ampersand operator e.g.

``````="year " & A3 & "/month " & A2 & "/week " & A1
``````

which will return e.g. "year 2022/month 8/week 1".

• In your formula `=DATE(2022, 8, (A1-1)*7)`, the day for the first week is zero. Oct 14, 2022 at 14:39
• @PierU thanks, forgot the +1. Now corrected. Oct 14, 2022 at 18:29