Date from Week Number

Vincent Gatto

I'm currently using a sheet with a =WEEKNUMBER([Date]@row) formula to group using the Pivot Table App. In the pivot table, I would like to display a date for the group in addition to the week number, as a "Week Start" column.

Example: The Pivot Table displays 42 for the week number. I'd like it to show with an additional column as 10/12/2020.

How might I do that? Thanks!

Ramzi K

@Vincent Gatto

Here's one way to do it:

=DATE(2019, 12, 30) + ((WEEKNUMBER(Date@row) - 1) * 7)

Cheers,

Ramzi

Ramzi K

@Vincent Gatto

Here's one way to do it:

=DATE(2019, 12, 30) + ((WEEKNUMBER(Date@row) - 1) * 7)

Cheers,

Ramzi

Amy Gonzalez

@Vincent Gatto and @Ramzi K

Did you get this to work? I'm getting an invalid data error when I use this formula. I create a year and week number column and am referencing them.

=DATE(Year@row, 12, 30) + ((WEEKNUMBER([Week Due]@row) - 1) * 7)

Not sure what I did wrong.

Thanks,

Amy

Vincent Gatto

I setup a helper column to determine the week number. Using the week number, I created a Pivot Smartsheet. On the Pivot Smartsheet, I used this formula:

=IF([Week Number]@row > 19, DATE(2020, 12, 30) + (([Week Number]@row) * 7 - 2), DATE(2022, 1, 4) + (([Week Number]@row - 1) * 7 - 1))

The "19" in the formula above has to do with what week it was and how many weeks of data I had going into the next year. I have to change the formula each year, but it does the trick.

If anyone has a better workaround, I'd appreciate it.