Auto Fill Dates

Mike Andreas

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

Mike Andreas

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

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:

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

Edward H

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

 

Ed