Ask Your Question
0

Do not display errors on with DATE

asked 2019-04-04 12:09:18 +0200

brice@ethersys.fr gravatar image

Hello everyone.

I did a calc sheet with dates and a cell telling how much days it reminds before it expires.

I would like to get rid of the Err:XXX when no value or nothing to calculate and i would like to get a blank cell instead of a XX/XX/00 date in my expirations cells.

I have my alert date let's say today 04/04/2019 (date fixed value) My Reminder/timer which is done like this : ="Reminds" & " " & DATEDIF(TODAY();L4;"d") & " " & "days" And L4 is my expiration date calcultated with +15 days like that : C*+15

The only tips i have found would be IF or IFERROR etc. but most of anwsers are for regular calculations with no dates.

Any advices ?

edit retag flag offensive close merge delete

Comments

So did you try to figure how to use IFERROR based on the advises (even if they are not about dates)? What had you tried, and how did it fail?

Mike Kaganski gravatar imageMike Kaganski ( 2019-04-04 12:14:16 +0200 )edit

Ok so thanks to Mike and erAck.

I resolved my issue the comments upside (or below) and i did my expiration date not displaying if nothing to calculate by simply :

=IF(A1="","",A1+7)

IF(Cell A1 = "" (nothing), "" nothing then do A1+7) to say cell A1 + 7 days

(explaining for others if they have the same issue)

Good day to you both !

brice@ethersys.fr gravatar imagebrice@ethersys.fr ( 2019-04-04 14:53:27 +0200 )edit

2 Answers

Sort by » oldest newest most voted
0

answered 2019-04-04 14:34:16 +0200

erAck gravatar image

Err:502 happens if in the call to DATEDIF(TODAY();L4;"d") cell L4 end date is smaller than start date, i.e. empty cell is 0 here. Suppressing all errors is almost never a good idea, so rather check the actual condition (and DATEDIF() for days is supefluous, dates can be simply subtracted to get the number of days):

=IF(TODAY()<=L4; "Reminds " & L4-TODAY() & " days"; "")
edit flag offensive delete link more

Comments

oh well thanks you ! By the way the anwser of Mike worked on my real sheet with blanking cells where i have no detection date, probably the options i modified in Tools > Options > Languages settings (unchecked some) + LibreOffice calc View Settings

Thanks for the explanations !

brice@ethersys.fr gravatar imagebrice@ethersys.fr ( 2019-04-04 14:42:37 +0200 )edit
0

answered 2019-04-04 12:20:51 +0200

brice@ethersys.fr gravatar image

Yes, last try (i dont remember all tries) was something like =iferror(TODAY();L4;"d"), and some else like if(blank= idont remember what)

and some else things... also tried to look in the tools/settings of libreoffice to see if i had an option to hide date format with 1900

edit flag offensive delete link more

Comments

Oh! so was it TODAY() that threw the error? ;-)

You need to use IFERROR on the whole formula, to replace the whole result with an empty string if an error happens in any inner part... like this:

=IFERROR("Reminds " & DATEDIF(TODAY();L4;"d") & " days"; "")
Mike Kaganski gravatar imageMike Kaganski ( 2019-04-04 12:35:47 +0200 )edit

yeah ! apparently yes ! your solution works like a charm i will try to add IFERROR(BLANK) or something like if i'm in trouble ill go back there

Thanks again !

brice@ethersys.fr gravatar imagebrice@ethersys.fr ( 2019-04-04 13:49:13 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2019-04-04 12:09:18 +0200

Seen: 32 times

Last updated: Apr 04