Forum Discussion
EDATE in IF statement giving wrong date
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!
