#NUM! error

BobBridges · #2 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.

Thread Tools
Show Printable Version Email this Page
Display Modes
Switch to Linear Mode Switch to Hybrid Mode Threaded Mode

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