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

Options
Edward H
Edward H
edited 12/09/19 in Archived 2017 Posts

I am trying to have cell automatically fill the following dates:

 

  • First day of the current week (i.e Monday)
  • Last day of the current week (i.e Sunday)
  • First day of the next week (i.e Monday)
  • Last day of the next week (i.e Sunday)
  • First day of the previous week (i.e Monday)
  • Last day of the previous week (i.e Sunday)
  • First day of the previous month
  • Last day of the previous month
  • First Day of Next Month

 

This will help with reporting. Any assistance would be greatly appreciated.

 

«1

Comments

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

    The other two took less time than I thought:

     

    • Last day of the previous month:

    =IF(WEEKDAY(DATE(YEAR(TODAY()), MONTH(TODAY()), DAY(TODAY()) - DAY(TODAY() - 1)) - 1) = "1", "Sunday", IF(WEEKDAY(DATE(YEAR(TODAY()), MONTH(TODAY()), DAY(TODAY()) - DAY(TODAY() - 1)) - 1) = "2", "Monday", IF(WEEKDAY(DATE(YEAR(TODAY()), MONTH(TODAY()), DAY(TODAY()) - DAY(TODAY() - 1)) - 1) = "3", "Tuesday", IF(WEEKDAY(DATE(YEAR(TODAY()), MONTH(TODAY()), DAY(TODAY()) - DAY(TODAY() - 1)) - 1) = "4", "Wednesday", IF(WEEKDAY(DATE(YEAR(TODAY()), MONTH(TODAY()), DAY(TODAY()) - DAY(TODAY() - 1)) - 1) = "5", "Thursday", IF(WEEKDAY(DATE(YEAR(TODAY()), MONTH(TODAY()), DAY(TODAY()) - DAY(TODAY() - 1)) - 1) = "6", "Friday", "Saturday"))))))

     

    • First Day of Next Month

    =IF(WEEKDAY(DATE(YEAR(TODAY()), MONTH(TODAY()) + 1, DAY(TODAY()) - DAY(TODAY() - 1))) = "1", "Sunday", IF(WEEKDAY(DATE(YEAR(TODAY()), MONTH(TODAY()) + 1, DAY(TODAY()) - DAY(TODAY() - 1))) = "2", "Monday", IF(WEEKDAY(DATE(YEAR(TODAY()), MONTH(TODAY()) + 1, DAY(TODAY()) - DAY(TODAY() - 1))) = "3", "Tuesday", IF(WEEKDAY(DATE(YEAR(TODAY()), MONTH(TODAY()) + 1, DAY(TODAY()) - DAY(TODAY() - 1))) = "4", "Wednesday", IF(WEEKDAY(DATE(YEAR(TODAY()), MONTH(TODAY()) + 1, DAY(TODAY()) - DAY(TODAY() - 1))) = "5", "Thursday", IF(WEEKDAY(DATE(YEAR(TODAY()), MONTH(TODAY()) + 1, DAY(TODAY()) - DAY(TODAY() - 1))) = "6", "Friday", "Saturday"))))))

     

    Screenshot showing all three in action (2/26/2017 used as today's date in these examples):

    Capture2.JPG

  • Mike Andreas
    Options

    I tried to post the answer to the other two twice, but the posts are not appearing for some reason.  Will check back later to see if the forum is still having issues and update with the equations...

  • Mike Andreas
    Options

    Last day of the previous month:

    =IF(WEEKDAY(DATE(YEAR(TODAY()), MONTH(TODAY()), DAY(TODAY()) - DAY(TODAY() - 1)) - 1) = "1", "Sunday", IF(WEEKDAY(DATE(YEAR(TODAY()), MONTH(TODAY()), DAY(TODAY()) - DAY(TODAY() - 1)) - 1) = "2", "Monday", IF(WEEKDAY(DATE(YEAR(TODAY()), MONTH(TODAY()), DAY(TODAY()) - DAY(TODAY() - 1)) - 1) = "3", "Tuesday", IF(WEEKDAY(DATE(YEAR(TODAY()), MONTH(TODAY()), DAY(TODAY()) - DAY(TODAY() - 1)) - 1) = "4", "Wednesday", IF(WEEKDAY(DATE(YEAR(TODAY()), MONTH(TODAY()), DAY(TODAY()) - DAY(TODAY() - 1)) - 1) = "5", "Thursday", IF(WEEKDAY(DATE(YEAR(TODAY()), MONTH(TODAY()), DAY(TODAY()) - DAY(TODAY() - 1)) - 1) = "6", "Friday", "Saturday"))))))

  • Mike Andreas
    Options

    First day of next month:

    =IF(WEEKDAY(DATE(YEAR(TODAY()), MONTH(TODAY()) + 1, DAY(TODAY()) - DAY(TODAY() - 1))) = "1", "Sunday", IF(WEEKDAY(DATE(YEAR(TODAY()), MONTH(TODAY()) + 1, DAY(TODAY()) - DAY(TODAY() - 1))) = "2", "Monday", IF(WEEKDAY(DATE(YEAR(TODAY()), MONTH(TODAY()) + 1, DAY(TODAY()) - DAY(TODAY() - 1))) = "3", "Tuesday", IF(WEEKDAY(DATE(YEAR(TODAY()), MONTH(TODAY()) + 1, DAY(TODAY()) - DAY(TODAY() - 1))) = "4", "Wednesday", IF(WEEKDAY(DATE(YEAR(TODAY()), MONTH(TODAY()) + 1, DAY(TODAY()) - DAY(TODAY() - 1))) = "5", "Thursday", IF(WEEKDAY(DATE(YEAR(TODAY()), MONTH(TODAY()) + 1, DAY(TODAY()) - DAY(TODAY() - 1))) = "6", "Friday", "Saturday"))))))

  • Mike Andreas
    Options

    Screenshot showing all three in action:

    Capture2.JPG

  • Mike Andreas
    Options

    I don't understand the need to have an equation for the first 6 requests you have, as those days will always be Monday and Sunday.  But for the other three, I am working on them.  Below is the answer to the first - "First day of previous month".  Will post the other two if/when I get them.

     

    I admit the code may not be efficient, but I can confirm it at least works.

     

    =IF(WEEKDAY(DATE(YEAR(TODAY()), MONTH(TODAY()) - 1, DAY(TODAY()) - DAY(TODAY() - 1))) = "1", "Sunday", IF(WEEKDAY(DATE(YEAR(TODAY()), MONTH(TODAY()) - 1, DAY(TODAY()) - DAY(TODAY() - 1))) = "2", "Monday", IF(WEEKDAY(DATE(YEAR(TODAY()), MONTH(TODAY()) - 1, DAY(TODAY()) - DAY(TODAY() - 1))) = "3", "Tuesday", IF(WEEKDAY(DATE(YEAR(TODAY()), MONTH(TODAY()) - 1, DAY(TODAY()) - DAY(TODAY() - 1))) = "4", "Wednesday", IF(WEEKDAY(DATE(YEAR(TODAY()), MONTH(TODAY()) - 1, DAY(TODAY()) - DAY(TODAY() - 1))) = "5", "Thursday", IF(WEEKDAY(DATE(YEAR(TODAY()), MONTH(TODAY()) - 1, DAY(TODAY()) - DAY(TODAY() - 1))) = "6", "Friday", "Saturday"))))))

     

    Capture1.JPG

  • Edward H
    Options

    Thanks Mike,

     

    Much appreciated. For the 'week' related items, it is the date (rather than day) that i am trying to get.

     

    Thanks again.

     

    Ed

  • Edward H
    Edward H
    edited 02/26/17
    Options

    Sorry Mike,

     

    For the 'Month' items I was trying to get the date only (rather than the day). Im sure your formula has this, but Im not smart enough to work it out!

     

    Thanks,

     

    Ed

  • Edward H
    Edward H
    edited 02/26/17
    Options

    Sorry Mike,

     

    For the 'Month' items I was trying to get the date only (rather than the day). Im sure your formula has this, but Im not smart enough to work it out!

     

    Thanks,

     

    Ed

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

    Ok, let me try again.  If all you're after if the date of the day, then these are the equations you want.  Note that I define week start and end by Sunday and Saturday respectively, not Monday and Sunday as you said in your answer.  If you really want the Monday/Sunday "set", these equations will be different.

     

    First day of the current week:

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

     

    Last day of the current week:

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

     

    First day of the next week:

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

     

    Last day of the next week:

    =IF(WEEKDAY((TODAY() + 7)) = "7", TODAY() + 7, IF(WEEKDAY((TODAY() + 8)) = "7", TODAY() + 8, IF(WEEKDAY((TODAY() + 9)) = "7", TODAY() + 9, IF(WEEKDAY((TODAY() + 10)) = "7", TODAY() + 10, IF(WEEKDAY((TODAY() + 11)) = "7", TODAY() + 11, IF(WEEKDAY((TODAY() + 12)) = "7", TODAY() + 1, TODAY() + 13))))))

     

    First day 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))

     

    Capture.PNG

  • Mike Andreas
    Options

    First day (Sunday) of the current week:

     

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

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

    Last day (Saturday) of the current week:

     

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

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

    First day (Sunday) of the next week:

     

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

  • Mike Andreas
    Options

    Last day (Saturday) of the next week:

     

    =IF(WEEKDAY((TODAY() + 7)) = "7", TODAY() + 7, IF(WEEKDAY((TODAY() + 8)) = "7", TODAY() + 8, IF(WEEKDAY((TODAY() + 9)) = "7", TODAY() + 9, IF(WEEKDAY((TODAY() + 10)) = "7", TODAY() + 10, IF(WEEKDAY((TODAY() + 11)) = "7", TODAY() + 11, IF(WEEKDAY((TODAY() + 12)) = "7", TODAY() + 1, TODAY() + 13))))))

  • Mike Andreas
    Options

    First day (Sunday) of the previous week:

     

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

This discussion has been closed.