Do not display errors on with DATE

[brice1]

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 ?

[mikekaganski]

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?

[brice1]

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 !

[brice1]

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

[mikekaganski]

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"; "")

[brice1]

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 !

[erAck]

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"; "")

[brice1]

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 !