EDATE: a quirk of the dates

It’s the day before Thanksgiving and I thought I would do a post before doing some feasting. I’m continuing on writing about some “findings” that I encountered while working on a project.

This time it is about the function EDATE and its curious quirk. But it does make sense for the quirk.

Little background

First, I must set the scene about a tiny portion of the project.

I needed to create tables of monthly calculations, depending on the user’s input of the start date. So, if the user input 5/10/2025 (U.S. format, so May 10, 2025) as the start date, then the table would consist of a column of dates in the form of the following: 5/10/2025, 6/10/2025, 7/10/2025, 8/10/2025…

The dates extend out as far as needed, basically until money runs out.

So, how would you set up the formula? Do you simply add 30 days to the first date (5/10/2025) and do the same calculation on out?

(Ugh! Why is the image so huge? Sorry about that.) Do you see the day portion of the date switch? February is totally missed.

Let’s try 31 days.

I think you are now starting to see the issue about the day portion of the date.

Function Edate

The function EDATE helps get around the worst of the issue. Its formula format is actually EDATE(start date, # of months).

Here’s what happened when I used EDATE(reference cell, 1).

It is not perfect, but it is much better than the using adding of 30 or 31 days to a date.

But the visual aspect of the dates bugs me: I want it to be consistent with the initial input. So if the day is 30th, then all other months except February should show 30th. But as you can see, EDATE does not lead to that result.

My workaround

Since I was creating the file for others to use and I didn’t want to cause consternation over the appearance of the dates running down the table, I decided to make the visual format of the dates to be MM/YYYY.

You can right click the cell(s) with the dates, go to “Format Cells” and then finally in “Custom”, chose or type mm/yyyy if you want something like 11/2025 (for November 2025).

This format method will create a column of dates showing just the month and year.

And that was fine since my real intention was to just show the monthly calculations.

Closing

I think my next post will be to describe some of the experiments with Agent Mode while working on this project.