Date (mm/dd/yyyy) from Week number

I am trying to get the date displayed from a week number. For instance this week is the 27th week of the year. I am trying to get it to display 07/05/2021 (the first day in the week).

I was thinking of converting the week to day of the year (i.e. 27 * 7 = 189) and using the 189 to convert to a date, but I can't figure out how. Does someone know how I would be able to do this?

Tags:

Best Answer

  • Toufong Vang
    Toufong Vang ✭✭✭✭✭
    edited 07/10/21 Answer ✓

    Try this...

    =IF(WEEKDAY(DATE(Year@row,1,1) + Week@row*7) = 1, DATE(Year@row,1,1) + Week@row*7, (DATE(Year@row,1,1) + Week@row*7) - (WEEKDAY(DATE(Year@row,1,1) + Week@row*7) - 1))

    = IF(WEEKDAY(DATE(Year@row,1,1) + Week@row*7) = 1
      ,DATE(Year@row,1,1) + Week@row*7
      ,(DATE(Year@row,1,1) + Week@row*7) - (WEEKDAY(DATE(Year@row,1,1) + Week@row*7) - 1) )
    

    The start of the week is Sunday where WEEKDAY() of some date equals 1.

    Change "1" to "2" if you want Monday.

    Why this works...

    • Adding 27 * 7 (189) to 01/01/2021 DATE(Year@row,1,1) + Week@row*7 gets you to 7/9/2021.
    • WEEKDAY() of 7/9/2021 = 6, which is 5 away from 1.
    • Subtracting 5 days from 7/9/2021 to gets you to 7/4/2021 of which WEEKDAY() is 1.

Answers

  • Toufong Vang
    Toufong Vang ✭✭✭✭✭
    edited 07/10/21 Answer ✓

    Try this...

    =IF(WEEKDAY(DATE(Year@row,1,1) + Week@row*7) = 1, DATE(Year@row,1,1) + Week@row*7, (DATE(Year@row,1,1) + Week@row*7) - (WEEKDAY(DATE(Year@row,1,1) + Week@row*7) - 1))

    = IF(WEEKDAY(DATE(Year@row,1,1) + Week@row*7) = 1
      ,DATE(Year@row,1,1) + Week@row*7
      ,(DATE(Year@row,1,1) + Week@row*7) - (WEEKDAY(DATE(Year@row,1,1) + Week@row*7) - 1) )
    

    The start of the week is Sunday where WEEKDAY() of some date equals 1.

    Change "1" to "2" if you want Monday.

    Why this works...

    • Adding 27 * 7 (189) to 01/01/2021 DATE(Year@row,1,1) + Week@row*7 gets you to 7/9/2021.
    • WEEKDAY() of 7/9/2021 = 6, which is 5 away from 1.
    • Subtracting 5 days from 7/9/2021 to gets you to 7/4/2021 of which WEEKDAY() is 1.
  • Thank you. This was really helpful!

  • @Toufong Vang Hello, would there be any columns required for this to work. I made a Year and week column and reference them with your formula, but I get Invalid Column Valid error. I would appreciate your help. Thanks

  • CNaenfeldt
    CNaenfeldt ✭✭✭

    @Toufong Vang I've tried this but I don't get it to work.

    My week is 50, and the start date of that week should be 12/11/2023, however when I use the formula above it gives me 12/18/2023.

    =IF(WEEKDAY(DATE(Year@row, 1, 1) + Week@row * 7) = 2, DATE(Year@row, 1, 1) + Week@row * 7, (DATE(Year@row, 1, 1) + Week@row * 7) - (WEEKDAY(DATE(Year@row, 1, 1) + Week@row * 7) - 2))

    Any suggestion on what I'm doing wrong?


    Thank you!

  • Toufong Vang
    Toufong Vang ✭✭✭✭✭
    edited 11/21/23

    Give these a try.

    SUNDAY

    =IF(WEEKDAY(DATE(Year@row, 1, 1)) = 1, ((Week@row - 1) * 7) + DATE(Year@row, 1, 1), ((Week@row - 1) * 7) + DATE(Year@row, 1, 1) + ((8 - WEEKDAY(DATE(Year@row, 1, 1)) + DATE(Year@row, 1, 1)) - DATE(Year@row, 1, 1)))

    MONDAY

    =IF(WEEKDAY(DATE(Year@row, 1, 1)) = 1, ((Week@row - 1) * 7) + DATE(Year@row, 1, 1) + 1, IF(WEEKDAY(DATE(Year@row, 1, 1)) = 2, (Week@row * 7) + DATE(Year@row, 1, 1), ((Week@row - 1) * 7) + DATE(Year@row, 1, 1) + ((8 - WEEKDAY(DATE(Year@row, 1, 1)) + DATE(Year@row, 1, 1)) - DATE(Year@row, 1, 1) + 1)))

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!