Forum Discussion
Issue with IF multiple formula with 3 outcomes
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()
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 That worked 100%.... thanks a lot, you made my day.
Riny_van_Eekelen Thank you for the reply. You are awesome. It worked perfectly.
Albaman83 Great!
