Issue with IF multiple formula with 3 outcomes

[Albaman83]

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

[#2 · Accepted 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()

[#3]

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

[#4]

@Albaman83 Great!

[#5]

@Riny_van_Eekelen

Hi in same context, how i can use this formula to give result as "Expired" if i already am using Formula =G2-TODAY()

to calculate numbers of days remaining in expiry from today to a future date? So my formula correctly gives me result as No of days remaining till expiry e.g. 16 for 16 days to go, but gives - for same day expiry and (3) if the expiry date has passed 3 days.

I want it to give result as EXPIRED if the result of my above formula is <=0, that is the date is passed.

Thanks.

[#6]

@jackinthebox340 Try this:

=IF(G2<TODAY(),"Expired",G2-TODAY())

.... and format the cell(s) with the formula as "General". Would that work for you? If not, please clarify your issue. 

[#7]

@Riny_van_Eekelen That worked 100%.... thanks a lot, you made my day.