SOLVED

Issue with IF multiple formula with 3 outcomes

%3CLINGO-SUB%20id%3D%22lingo-sub-1444674%22%20slang%3D%22en-US%22%3EIssue%20with%20IF%20multiple%20formula%20with%203%20outcomes%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1444674%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20Guys%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20the%20following%20formula%3A%3C%2FP%3E%3CP%3E%3DIF(%5B%40%5BDelivered%3F%5D%5D%3D%22NO%22%2C%22Warranty%20not%20Active%22%2CIF(DATEDIF(TODAY()%2CH263%2C%22D%22)%26lt%3B0%2C%22Warranty%20Expired%22%2CCONCATENATE(DATEDIF(TODAY()%2CH263%2C%22D%22)%2C%22%20Warranty%20Days%20Remaining%22)))%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20issue%20is%20the%20%22Warranty%20Expired%22%20doesn't%20appear%20when%20the%20number%20of%20days%20is%20less%20than%200%20and%20shows%20a%20%23NUM!%20The%20rest%20works%20perfect.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20in%20advance%20for%20your%20advise.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAlbert%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1444674%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1444719%22%20slang%3D%22en-US%22%3ERe%3A%20Issue%20with%20IF%20multiple%20formula%20with%203%20outcomes%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1444719%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F403176%22%20target%3D%22_blank%22%3E%40Riny_van_Eekelen%3C%2FA%3E%26nbsp%3BThank%20you%20for%20the%20reply.%20You%20are%20awesome.%20It%20worked%20perfectly.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1444735%22%20slang%3D%22en-US%22%3ERe%3A%20Issue%20with%20IF%20multiple%20formula%20with%203%20outcomes%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1444735%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F690832%22%20target%3D%22_blank%22%3E%40Albaman83%3C%2FA%3E%26nbsp%3BGreat!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1444701%22%20slang%3D%22en-US%22%3ERe%3A%20Issue%20with%20IF%20multiple%20formula%20with%203%20outcomes%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1444701%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F690832%22%20target%3D%22_blank%22%3E%40Albaman83%3C%2FA%3E%26nbsp%3BAvoid%20using%20DATEDIF()%20for%20this.%20It's%20not%20an%20official%20Excel%20function%20and%20only%20exists%20%22%3CU%3E%3CEM%3Eto%26nbsp%3B%3C%2FEM%3E%3C%2FU%3E%3CSPAN%3E%3CU%3E%3CEM%3Esupport%20older%20workbooks%20from%20Lotus%201-2-3.%20Furthermore%2C%20The%20DATEDIF%20function%20may%20calculate%20incorrect%20results%20under%20certain%20scenarios%22%3C%2FEM%3E%3C%2FU%3E.%20(Source%3A%20MS%20support%20page%20in%20the%20DATEDIF%20function).%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3EDATEDIF%20expects%20a%20start%20date%20in%20the%20first%20element%20and%20an%20end%20date%20in%20the%20second.%20If%20the%20first%20element%20is%20greater%20than%20the%20second%20you%20get%20!NUM%23.%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3EUse%20standard%20operators%20in%20stead.%20Like%3A%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DIF(date%20%26lt%3B%20TODAY()%2C%20%22Expired%22%2C%20date%20-%20TODAY()%26amp%3B%20%22%20days%20remaining.%22)%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EOnce%20it%20has%20passed%20the%20first%20test%20you%20know%20that%20warranty%20has%20not%20expired%20yet%20and%20you%20can%20safely%20calculate%20the%20days%20remaining%20by%20calculating%20date%20-%20TODAY()%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
New Contributor

Hi Guys,

 

I have the following formula:

=IF([@[Delivered?]]="NO","Warranty not Active",IF(DATEDIF(TODAY(),H263,"D")<0,"Warranty Expired",CONCATENATE(DATEDIF(TODAY(),H263,"D")," Warranty Days Remaining")))

 

The issue is the "Warranty Expired" doesn't appear when the number of days is less than 0 and shows a #NUM! The rest works perfect. 

 

Thank you in advance for your advise.

 

Albert

3 Replies
Highlighted
Best Response confirmed by Albaman83 (New Contributor)
Solution

@Albaman83 Avoid using DATEDIF() for this. It's not an official Excel function and only exists "to support older workbooks from Lotus 1-2-3. Furthermore, The DATEDIF function may calculate incorrect results under certain scenarios". (Source: MS support page in the DATEDIF function).

 

DATEDIF expects a start date in the first element and an end date in the second. If the first element is greater than the second you get !NUM#.

 

Use standard operators in stead. Like:

 

=IF(date < TODAY(), "Expired", date - TODAY()& " days remaining.")

 

Once it has passed the first test you know that warranty has not expired yet and you can safely calculate the days remaining by calculating date - TODAY()

Highlighted

@Riny_van_Eekelen Thank you for the reply. You are awesome. It worked perfectly.

Highlighted