#1
|
|||
|
|||
#NUM! error
=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 |
#2
|
||||
|
||||
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") Code:
=INT(DATEDIF(J5,J3,"d")/7)&" Weeks "&MOD(DATEDIF(J5,J3,"d"),7)&" Days" 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") Code:
=IF(J3<J5,"Expired",INT(DATEDIF(J5,J3,"d")/7)&" Weeks "&MOD(DATEDIF(J5,J3,"d"),7)&" Days") |
#3
|
||||
|
||||
Bob
there is no ERROR function in Excel that I know of One can use IFERROR ( post 2003 versions) or IF(ISERROR....
__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post |
#4
|
||||
|
||||
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") Code:
=IFERROR(INT(DATEDIF(J5,J3,"d")/7)&" Weeks "&MOD(DATEDIF(J5,J3,"d"),7)&" Days","Expired") |
#5
|
|||
|
|||
Quote:
It seems the simplest formula works very well. Very many thanks for your expert help..Wish I'd thought of it ...lol |
#6
|
||||
|
||||
FWIW you can replace
INT(DATEDIF(J5,J3,"d")/7) with QUOTIENT(datedif(j5,j3,"d"),7))
__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post |
#7
|
|||
|
|||
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 |