Formula using Name does not work when content is a date

New Contributor

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 Replies


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

best response confirmed by Jean-Paul_Gruffeille (New Contributor)

@Hans Vogelaar 


Yes, I did this change and it works. 


Thank you.