May 05 2022 06:35 AM
I want to test the current date against a reference date to define which action to apply depending on whether the current date is before or after the reference date.
The reference date is defined by a Name (DATE_NEW_TARIF) and is valid for the entire workbook:
The formula in cell Guides!$G$4 is: =CONCATENATE("01/09/";YEAR)
If I have the current date in cell E56 and run the following test in cell E57:
(E57)=IF(E56<DATE_NEW_TARIF;"no change";"new tarif")
the result is ALWAYS TRUE (i.e. "no change"° regardless of the date in cell E56.
If I run the same test in E57
(E57)=IF(F56<F55;"no change";"new tarif")
but this time writing in cell E55 the same date as DATE_NEW_TARIF, i.e. 01/09/2022, then the result of the IF works perfectly well, i.e. I get "no change" if E55 is before the 01/09/2022 and "new tarif" is it is after.
All the cells E55, E56 and Guide!$G$4 are in the Short Date format.
Why is it not working and how can I get this to work?
The plan is to use the formula across several Sheets.
Thank you
May 05 2022 07:14 AM
=CONCATENATE("01/09/";YEAR) returns a text string, not a date. Try changing it to =DATE(YEAR;9;1)
May 05 2022 07:51 AM
SolutionMay 05 2022 07:51 AM
Solution