EDATE Function displaying wrong info?

Question

Hello everyone, I'm sure everyone has ran into the same problem when calculating dates as I have. 

A few questions I have are:

What does excel use as default to calculate a month?  30 days?

When does excel decide to use 31 days instead of 30?

When adding 1 month does the start date count as day one which in theory would mean that the formula is +29 days? (assuming EDATE calculates by 30's)

and my final question is when using the EDATE function I enter 10/30/2013 and tell Excel to add one month. It gives me 11/30/2013. Also when I enter 10/31/2013 and ask it to add one month it still gives me 11/30/2013.  Whats the explanation for this?

If you were to count starting from 10/30/2013 and add 30 days the final day would be 11/28/2013

Answer 1

Interesting. So what would be the best way to calculate the months and actually show me the correct date?  I was told by a friend that I'm just not asking the question properly or using the correct function for what I want. 

Thanks for your help!

Answer 2

This has been very helpful. Whatever number I chose to add to "A1" should be -1 no? Since "A1" already is day 1?

Hi,

It's no different to basic math. If we want 1+6 then that's what we do, we don't do 1+5 because we already have 1. If you want to add 14 days to a date then it's date+14.

Answer 3

This has been very helpful. Whatever number I chose to add to "A1" should be -1 no? Since "A1" already is day 1?

Answer 4

Interesting. So what would be the best way to calculate the months and actually show me the correct date?  I was told by a friend that I'm just not asking the question properly or using the correct function for what I want. 

Thanks for your help!

Hi,

The problem with the calendar month is that it has no single empirical value, it can be anything from 28 to 31 days. The answer to what you should do depends on what you're trying to achieve. To add n number of days to a date then you don't need EDATE you can simply use this:-

=A1+30

Where A1 is the start date, You can add any number of days in this way. If it's something else then please clarify.

Answer 5

Hi,

EDATE will return a date n months before/after the start date and; when it can, return the same day of the start date so the use of 28, 29, 30 or 31 days is a function of the start date.

Sometimes it can't return the same day as in your example of 31 October because there is no 31st November so it uses 30 Nov. Why? A judgement call from whoever wrote the function and it seems to me entirely logical