Auto Fill Dates

Edward H

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.

 

Mike Andreas

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

Mike Andreas

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

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

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

Screenshot showing all three in action:

Mike Andreas

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

 

Edward H

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

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

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

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

 

Mike Andreas

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

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

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

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

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