Week Ending Date

jgomeztagle

Hello, I am creating a Safety Audit Database.  Similar to Scheduling principles, we keep track of all data entries by two dates.  ACTUAL DATE and WEEK ENDING DATE (where Week Ending date is Sunday)

We use a formula in Excel to do this but I am not able to replicate such formula in smartsheet.

Does anyone know how to do this? change an actual date from one column to a WEEK ENDING into another column.  This way I can TIE all DATA based on Project and Week Ending Date to match all of our current reporting.

The formula in Excel is:

=(7-WEEKDAY(A1,2))+A1

Any help is appreciated.

Thanks,

Shaine Greenwood

Hi jgomeztagle!

If I'm understanding you correctly, you're wanting to return the next Sunday as your week ending date. Smartsheet has to do this differently, because the WEEKDAY function doesn't allow you to start counting from a specific day of the week like Excel's does with the "2" argument in your example.

This formula, albeit long, should do the trick:

=IF(WEEKDAY([Start Date]9) = 1, [Start Date]9 + 7, IF(WEEKDAY([Start Date]9) = 2, [Start Date]9 + 6, IF(WEEKDAY([Start Date]9) = 3, [Start Date]9 + 5, IF(WEEKDAY([Start Date]9) = 4, [Start Date]9 + 4, IF(WEEKDAY([Start Date]9) = 5, [Start Date]9 + 3, IF(WEEKDAY([Start Date]9) = 6, [Start Date]9 + 2, IF(WEEKDAY([Start Date]9) = 7, [Start Date]9 + 1)))))))

Make sure to change [Start Date]9 in the formula above to reference a cell in your own sheet.

 

J. Craig Williams

jgomeztagle

Try this:

=IF(WEEKDAY([Date]23) = 1, [Date]23, (8 - WEEKDAY([Date]23)) + [Date]23)

If the [Date]23 is Sunday, it will return that date, otherwise it will return the upcoming Sunday.

The formula is very close to Excel's.

Craig

Ky|e

Hello,

I am trying to return the Friday after the set date. I tried to tinker with the formula and the closest I could get is as follows:

=IF(WEEKDAY([Due Date]$1) = 7, [Due Date]$1, (6 - WEEKDAY([Due Date]$1)) + [Due Date]$1)

The only day which I see a problem with is Saturday, which will pull the same day of Saturday. Would you happen to know how I could adjust this so that the Saturday (take for instance 4/13/19, will populate a date of the upcoming Friday 4/19/19?

Appreciate your help with this.

Thank you,