microsoft-excel date: Updated Mon, 06 Jun 2022 04:24:53 GMT

Excel Formula > Selecting specific date of next month

I am trying to set up an automatic cell in Excel to show the same day of the Next Month based on the current month.

In other words, if today is 11/20/2020, and I want to show the 13th day of the Next month, I'd want the cell to end up showing 12/13/2020.

I know that EDATE can help me calculate 1 month into the future based on a start date, but I dont want to have to change the start date on my spreadsheet every month.

To give a little background, this is basically a spreadsheet for paying bills. Some bills are due on the day day each month. I'd like to auto calculate the actual due date, and then use conditional formatting to highlight which bills are due within the next 14 days. I think I have the conditional formatting piece ready, I just dont know how to calculate the dates as I laid out above.

Solution

GOAL: Display the date of the 13^th of the month after the month given by the TODAY() function.

How about:

=DATE(YEAR(TODAY()),MONTH(TODAY())+1,13)

Note: the formula will correctly roll forward from December to January of the next year.

Comments (2)

+0 – Thanks! this is perfect — Nov 20, 2020 at 19:13
+0 – @TomD You are quite welcome! — Nov 20, 2020 at 19:14

External Links

External links referenced by this document:

https://i.stack.imgur.com/exwsF.png

References

https://superuser.com/questions/1603765