#### 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
edited 12/09/19

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

• ✭✭✭
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):

• ✭✭✭
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...

• ✭✭✭
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"))))))

• ✭✭✭
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"))))))

• ✭✭✭
Options

Screenshot showing all three in action:

• ✭✭✭
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"))))))

• 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

• 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

• 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

• ✭✭✭
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))

• ✭✭✭
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))))))

• ✭✭✭
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))))))

• ✭✭✭
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))))))

• ✭✭✭
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))))))

• ✭✭✭
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.