# Date (mm/dd/yyyy) from Week number

Options

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:

• ✭✭✭✭✭
Options

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.

• ✭✭✭✭✭
Options

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.
• Options

Thank you. This was really helpful!

• Options

@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

• ✭✭✭
Options

@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!

• ✭✭✭✭✭
edited 11/21/23
Options

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!