Help to resolve IF function for excel

%3CLINGO-SUB%20id%3D%22lingo-sub-3508928%22%20slang%3D%22en-US%22%3EHelp%20to%20resolve%20IF%20function%20for%20excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3508928%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20all%2C%3CBR%20%2F%3E%3CBR%20%2F%3EI%20have%20a%20date%20cell%20A1%3DApr-24%3CBR%20%2F%3EI%20input%20this%20formula%20%3DIF(A1%26lt%3B%3DDATE(YEAR(TODAY())%2C12%2C31)%2CCondition1%2CCondition2)%3CBR%20%2F%3EWill%20does%20this%20formula%20not%20mean%20if%20A1%20is%20within%20this%20year%2C%20Condition1%20should%20be%20fulfilled%3F%3CBR%20%2F%3EI%20got%20Condition2%20result%20returned%20to%20me.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-3508928%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3509015%22%20slang%3D%22en-US%22%3ERe%3A%20Help%20to%20resolve%20IF%20function%20for%20excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3509015%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1424775%22%20target%3D%22_blank%22%3E%40prickyprickl35%3C%2FA%3E%26nbsp%3BThat%20formula%20will%20return%20Condition1%20for%20any%20date%20in%20A1%20that%20is%20%3CU%3Eon%3C%2FU%3E%20or%20%3CU%3Ebefore%3C%2FU%3E%20the%20end%20of%20the%20current%20year.%20Not%20only%20within%20this%20year.%20Also%20dates%20from%20last%20year%20or%20ten%20years%20ago.%20Anything%20else%20returns%20Condition2.%3C%2FP%3E%3CP%3EAssuming%20%22Apr-24%22%20is%20a%20real%20date%20(i.e.%20a%20proper%20date%20value)%2C%20merely%20formatted%20to%20be%20shown%20as%20Mmm-yy%2C%20then%20Condition2%20would%20be%20correct%20as%20it%20is%20after%20Dec%2031%2C%202021.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20A1%20contains%20a%20text%20that%20looks%20like%20a%20date%20Mmm-yy%2C%20you%20will%20always%20get%20Condition2%20as%20a%20text%20is%20considered%20greater%20than%20a%20number.%20And%20real%20dates%20in%20Excel%20are%20in%20fact%20numbers.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Visitor

Hi all,

I have a date cell A1=Apr-24
I input this formula =IF(A1<=DATE(YEAR(TODAY()),12,31),Condition1,Condition2)
Will does this formula not mean if A1 is within this year, Condition1 should be fulfilled?
I got Condition2 result returned to me.

1 Reply

@prickyprickl35 That formula will return Condition1 for any date in A1 that is on or before the end of the current year. Not only within this year. Also dates from last year or ten years ago. Anything else returns Condition2.

Assuming "Apr-24" is a real date (i.e. a proper date value), merely formatted to be shown as Mmm-yy, then Condition2 would be correct as it is after Dec 31, 2021.

 

If A1 contains a text that looks like a date Mmm-yy, you will always get Condition2 as a text is considered greater than a number. And real dates in Excel are in fact numbers.