SOLVED

Formula using Name does not work when content is a date

%3CLINGO-SUB%20id%3D%22lingo-sub-3323429%22%20slang%3D%22en-US%22%3EFormula%20using%20Name%20does%20not%20work%20when%20content%20is%20a%20date%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3323429%22%20slang%3D%22en-US%22%3E%3CP%3EI%20want%20to%20test%20the%20current%20date%20against%20a%20reference%20date%20to%20define%20which%20action%20to%20apply%20depending%20on%20whether%20the%20current%20date%20is%20before%20or%20after%20the%20reference%20date.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20reference%20date%20is%20defined%20by%20a%20Name%26nbsp%3B(DATE_NEW_TARIF)%20and%20is%20valid%20for%20the%20entire%20workbook%3A%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22JeanPaul_Gruffeille_0-1651756634841.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F369466i9D3497389EB1FBA0%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22JeanPaul_Gruffeille_0-1651756634841.png%22%20alt%3D%22JeanPaul_Gruffeille_0-1651756634841.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3EThe%20formula%20in%20cell%20Guides!%24G%244%20is%3A%20%3DCONCATENATE(%2201%2F09%2F%22%3BYEAR)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20I%20have%20the%20current%20date%20in%20cell%20E56%20and%20run%20the%20following%20test%20in%20cell%20E57%3A%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B(E57)%3DIF(E56%3CDATE_NEW_TARIF%3E%3C%2FDATE_NEW_TARIF%3E%3C%2FP%3E%3CP%3Ethe%20result%20is%20ALWAYS%20TRUE%20(i.e.%20%22no%20change%22%C2%B0%20regardless%20of%20the%20date%20in%20cell%20E56.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20I%20run%20the%20same%20test%20in%20E57%3C%2FP%3E%3CP%3E%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B(E57)%3DIF(F56%3CF55%3E%3C%2FF55%3E%3C%2FP%3E%3CP%3Ebut%20this%20time%20writing%20in%20cell%20E55%20the%20same%20date%20as%20DATE_NEW_TARIF%2C%20i.e.%2001%2F09%2F2022%2C%20then%20the%20result%20of%20the%20IF%20works%20perfectly%20well%2C%20i.e.%20I%20get%20%22no%20change%22%20if%20E55%20is%20before%20the%2001%2F09%2F2022%20and%20%22new%20tarif%22%20is%20it%20is%20after.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAll%20the%20cells%20E55%2C%20E56%20and%20Guide!%24G%244%20are%20in%20the%20Short%20Date%20format.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWhy%20is%20it%20not%20working%20and%20how%20can%20I%20get%20this%20to%20work%3F%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20plan%20is%20to%20use%20the%20formula%20across%20several%20Sheets.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-3323429%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EOffice%20365%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3324876%22%20slang%3D%22en-US%22%3ERe%3A%20Formula%20using%20Name%20does%20not%20work%20when%20content%20is%20a%20date%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3324876%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F127945%22%20target%3D%22_blank%22%3E%40Hans%20Vogelaar%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYes%2C%20I%20did%20this%20change%20and%20it%20works.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3324041%22%20slang%3D%22en-US%22%3ERe%3A%20Formula%20using%20Name%20does%20not%20work%20when%20content%20is%20a%20date%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3324041%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1381905%22%20target%3D%22_blank%22%3E%40Jean-Paul_Gruffeille%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3DCONCATENATE(%2201%2F09%2F%22%3BYEAR)%20returns%20a%20text%20string%2C%20not%20a%20date.%20Try%20changing%20it%20to%20%3DDATE(YEAR%3B9%3B1)%3C%2FP%3E%3C%2FLINGO-BODY%3E
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.