#NUM! error

BobBridges · #1 02-01-2014, 08:51 PM

You want the IF and ERROR functions. The long and complex way of doing it is this:

Code:

=IF(ERROR(INT(DATEDIF(J5,J3,"d")/7)&" Weeks "&MOD(DATEDIF(J5,J3,"d"),7)&" Days"),"Expired",INT(DATEDIF(J5,J3,"d")/7)&" Weeks "&MOD(DATEDIF(J5,J3,"d"),7)&" Days")

Me, I'd put this in K7:

Code:

=INT(DATEDIF(J5,J3,"d")/7)&" Weeks "&MOD(DATEDIF(J5,J3,"d"),7)&" Days"

Then in J7 I'd just say =IF(ERROR(K7),"Expired",K7).

On second thought, you could make it simpler. Since it's the DATEDIF function that's generating the error, you could say

Code:

=IF(ERROR(DATEDIF(J5,J3,"d")),"Expired",INT(DATEDIF(J5,J3,"d")/7)&" Weeks  "&MOD(DATEDIF(J5,J3,"d"),7)&" Days")

Or even just

Code:

=IF(J3<J5,"Expired",INT(DATEDIF(J5,J3,"d")/7)&" Weeks  "&MOD(DATEDIF(J5,J3,"d"),7)&" Days")

Yeah, that'd be best.

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Automation error Unknown error" message once they open the Excel file	hlina	Excel	1	10-08-2013 09:14 PM
Word Visual Basic error - run time error 504	crazymorton	Word	11	01-13-2012 04:32 AM
Microsoft office 2010 error 2908 and error 1935 !!!!!!heeeeellpppp!!!!!!!!!	bennypryde	Office	1	01-05-2012 03:33 PM
Runtime error 5487 - Word cannot complete the save to to file permission error	franferns	Word	0	11-25-2009 05:35 AM
Receive error cannot open this form because an error occurred in BCM 2007	bornhusker	Outlook	0	06-01-2009 10:28 AM