#NUM! error

jimboba · #1 02-01-2014, 05:16 PM

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

Cell J3 is a date in the future manually entered
Cell J5 is the date today generated by =TODAY()
Cell J7 has the above formula which shows weeks and Days between the two dates.
This all works fine until the date in J3 is less than todays date ie yesterdays date or any date in the past.
in which case Cell J7 shows the above error. I would like that cell to display the message "Expired"
Help appreciated. thanks

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.

Pecoflyer · #3 02-02-2014, 02:39 AM

Bob
there is no ERROR function in Excel that I know of
One can use IFERROR ( post 2003 versions) or IF(ISERROR....

BobBridges · #4 02-02-2014, 06:43 AM

I did that from memory, and you know, even as I wrote it I had a niggling suspicion that I should look it up. But no, I thought to myself, I KNOW this stuff; I don't need no stinkin' manual! He's right, of course, jimboba; it's not ERROR, it's ISERROR.

I still think, though, that it's even simpler to use the last one:

Code:

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

<Double take> IFERROR? I don't think I ever knew about that one. ...Oh, cool! (I just looked it up.) Ok, jimboba, here's another alternative:

Code:

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

Yeah, probably even better.

jimboba · #5 02-03-2014, 03:03 AM

Quote:

Originally Posted by BobBridges

I did that from memory, and you know, even as I wrote it I had a niggling suspicion that I should look it up. But no, I thought to myself, I KNOW this stuff; I don't need no stinkin' manual! He's right, of course, jimboba; it's not ERROR, it's ISERROR.

I still think, though, that it's even simpler to use the last one:

Code:

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

<Double take> IFERROR? I don't think I ever knew about that one. ...Oh, cool! (I just looked it up.) Ok, jimboba, here's another alternative:

Code:

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

Yeah, probably even better.

Hi Bob
It seems the simplest formula works very well.
Very many thanks for your expert help..Wish I'd thought of it ...lol

Pecoflyer · #6 02-03-2014, 12:46 PM

FWIW you can replace

INT(DATEDIF(J5,J3,"d")/7) with QUOTIENT(datedif(j5,j3,"d"),7))

jimboba · #7 02-03-2014, 04:06 PM

Hi Pecoflyer,
Thanks for your input as well.
Regards
Jim

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