Formula using Name does not work when content is a date

[Jean-Paul_Gruffeille]

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

[#2]

@Jean-Paul_Gruffeille 

=CONCATENATE("01/09/";YEAR) returns a text string, not a date. Try changing it to =DATE(YEAR;9;1)

[#3 · Accepted solution]

@Hans Vogelaar 

Yes, I did this change and it works. 

Thank you.