EDATE in IF statement giving wrong date

[PhilLay]

Hi,

I am finding when using this formula:

=IF(TODAY()>=EDATE($A3,B$2*12),EDATE($A3,B$2*12),0)

In the following table below, at year elapsed 27, the date outputted goes to 0/01/1900 when it should be 01/11/2024. However, when using EDATE in isolation, eg. EDATE(A3,F2*12) it executes correctly. I've tried using DATE(YEAR()+n,MONTH(),DAY()) as a substitute for EDATE and it still gives the same issue.

Can anyone identify what I've done wrong? Thanks!

	Year elapsed
Start Date	7	12	17	22	27	32
1/11/1997	1/11/2004	1/11/2009	1/11/2014	1/11/2019	0/01/1900	0/01/1900

[#2 · Accepted solution]

@PhilLay What date did you intend to input in cell A3? January 11, 1997 or November 1, 1997? Based on the output sample provided, the date format being used is d/mm/yyyy, which means Excel is reading it as November 1, 2024. The IF function is returning 0 in this case, because TODAY() is less than November 1, 2024. When formatted as a date, 0 is represented as January 0, 1900, hence 0/01/1900 is the result (d/mm/yyyy). Try inputting 11/01/1997 in cell A3 and see if that returns the expected results. If you would prefer to input dates in Excel as mm/dd/yyyy, you will need to go into your computer's regional and language settings to configure the desired Short Date format for your system.

[#3]

@djclements Ah thank you! You made me realize I am an idiot and 0 is 0/01/1900.

[#4]

@PhilLay You're welcome. Dates are a very common source of confusion in Excel, so don't beat yourself up over it. 😉 Cheers!