Welcome to the Smartsheet Forum Archives


The posts in this forum are no longer monitored for accuracy and their content may no longer be current. If there's a discussion here that interests you and you'd like to find (or create) a more current version, please Visit the Current Forums.

Week Ending Date

Options
jgomeztagle
edited 12/09/19 in Archived 2017 Posts

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,

Comments

  • Shaine Greenwood
    Options

    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
    J. Craig Williams ✭✭✭✭✭✭
    Options

    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
    Ky|e
    edited 04/10/19
    Options

    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,

This discussion has been closed.