SOLVED

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:

JeanPaul_Gruffeille_0-1651756634841.png

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

@Jean-Paul_Gruffeille 

=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)
Solution

@Hans Vogelaar 

 

Yes, I did this change and it works. 

 

Thank you.