Connect with peers, share your expertise, and inspire what’s next in Smartsheet — from proven practices to practical how-to insights from fellow users and product experts.
Sign in to join the conversation:
The posts in this section are historical and no longer monitored for accuracy. If this discussion interests you and you'd like to join in, please visit the active Community to post and engage.
I am trying to have cell automatically fill the following dates:
This will help with reporting. Any assistance would be greatly appreciated.
The other two took less time than I thought:
=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"))))))
=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):
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...
Last day of the previous month:
First day of next month:
Screenshot showing all three in action:
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"))))))
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
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,
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))
=DATE(YEAR(TODAY()), MONTH(TODAY()), DAY(TODAY()) - DAY(TODAY() - 1)) - 1
=DATE(YEAR(TODAY()), MONTH(TODAY()) + 1, DAY(TODAY()) - DAY(TODAY() - 1))
First day (Sunday) of the current week:
Last day (Saturday) of the current week:
First day (Sunday) of the next week:
Last day (Saturday) of the next week:
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))))))
