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.

Auto Fill Dates

2»

Comments

  • Last day (Saturday) of the previous week:

     

    =IF(WEEKDAY((TODAY() - 7)) = "7", TODAY() - 7, IF(WEEKDAY((TODAY() - 6)) = "7", TODAY() - 6, IF(WEEKDAY((TODAY() - 5)) = "7", TODAY() - 5, IF(WEEKDAY((TODAY() - 4)) = "7", TODAY() - 4, IF(WEEKDAY((TODAY() - 3)) = "7", TODAY() - 3, IF(WEEKDAY((TODAY() - 2)) = "7", TODAY() - 2, TODAY() - 1))))))

  • First day of the previous month:

    =DATE(YEAR(TODAY()), MONTH(TODAY()) - 1, DAY(TODAY()) - DAY(TODAY() - 1))

     

    Last day of the previous month:

    =DATE(YEAR(TODAY()), MONTH(TODAY()), DAY(TODAY()) - DAY(TODAY() - 1)) - 1

     

    First day of next month:

    =DATE(YEAR(TODAY()), MONTH(TODAY()) + 1, DAY(TODAY()) - DAY(TODAY() - 1))

  • Mike Andreas
    Mike Andreas ✭✭✭
    edited 02/26/17

    Note that my equations define week start and end as Sunday/Saturday.  If you really define week start/end by Monday/Sunday as you mentioned in your original question, the equations will be different.

     

    Here is a screenshot summarizing it all:

    Capture.PNG

  • J. Craig Williams
    J. Craig Williams ✭✭✭✭✭✭

    This also works:

     

    First day (Sunday) of the current week:

     

    = TODAY() + 1 - WEEKDAY(TODAY())

     

    Last day (Saturday) of the current week:

     

    = TODAY() + 7 - WEEKDAY(TODAY())

     

    Hope this helps.

     

    Craig

  • Thank you Mike and Craig. This is a huge help.

     

    Ed

This discussion has been closed.