Forum Discussion
Solved
EDATE in IF statement giving wrong date
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 |
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.
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.
djclements Ah thank you! You made me realize I am an idiot and 0 is 0/01/1900.
PhilLay You're welcome. Dates are a very common source of confusion in Excel, so don't beat yourself up over it. 😉 Cheers!
