Forum Discussion
Issue with IF multiple formula with 3 outcomes
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
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()
6 Replies
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()
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.
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.
Riny_van_Eekelen Thank you for the reply. You are awesome. It worked perfectly.
Albaman83 Great!
