Auto Increment Date

JackM98682

Hello everyone!

I'm trying to figure out how to roll my Week Ending value over to the next Saturday when my Today date is greater than or equal to the current Week End. Basically, I'm looking for something similar to "If Date => Week Ending, Week Ending +7" that won't send a circular reference error. Is this feasible?

Jeff Reisman

@JackM98682 There are a lot of roundabout ways to do this using various date functions.

One way I might do it is using a lookup sheet (call it "Saturdays") containing the week number and the Saturday date from each week. So you have a text/number WeekNum column and a date-type Saturday column. Start on row 1 with a WeekNum value of 1, and a Saturday value of 1/7/23, then in row 2, use these formulas:

=WeekNum1 + 1

=Saturday1 + 7

Then just ctrl-c/ctrl-v the formulas down into 51 more rows. The row numbers in the formulas will increment themselves.

Then you'll have a list with each week number for the year with its Saturday date.

One thing to remember is the week numbers in Smartsheet start on Mondays. So we'll have to account for Saturdays and Sundays in the formula.

=IF(WEEKDAY(Date@row) >= 6, INDEX({Create Ref to Saturdays sheet Saturday column}, MATCH(WEEKNUMBER(Date@row) + 1, {Create Ref to Saturdays sheet WeekNum column, 0)), INDEX({Create Ref to Saturdays sheet Saturday column}, MATCH(WEEKNUMBER(Date@row), {Create Ref to Saturdays sheet WeekNum column, 0)))

In English - If the value in Date column is a Saturday or Sunday, give me the Saturday date for the week number after this current week number; otherwise, give me the Saturday date for this week's week number.

JackM98682

@Jeff Reisman I believe that's exactly what I'm looking for. Thank you!

Jeff Reisman

https://community.smartsheet.com/discussion/comment/362476#Comment_362476

Fantastic!