IF(DATEVALUE) returning #VALUE!

Question

Hello,

I am using the following formula for cell H4: 

=IF(DATEVALUE(G4), E4*25%)

when a date is entered (in G4),  the amount in E4 is divided by 25 per cent. When there is no date there, it is returning, #VALUE! is there a way of replacing that #VALUE! with a 0 or blank?

Many thanks,

Answer 1

1. Do not put quotes around 0 (zero) if your intent is return a numeric value (which E4*25% is).
2. As I explained, DATEVALUE returns #VALUE if G4 contains a numeric date, as well as any text that is not recognized as a date.  Is that really your intent?(!).

Answer 2

Hello,

I fixed it using the formula:

=IFERROR(IF(DATEVALUE(G4), E4*25%),"0")

I'm trying to get a lot better at excel, it's a lot of fun!

Answer 3

Ostensibly:

=IF(ISNUMBER(DATEVALUE(G4)), E4*25%, "")

But I suspect that you really want:

=IF(G4="", "", E4*25%)

The issue is:  when does DATEVALUE return #VALUE, and what do you mean by "date is entered"?

DATEVALUE returns a numeric value ("date serial number") only if G4 contains text that Excel recognizes as a date.

But usually, when we enter something that Excel recognizes as a date, it is converted to a numeric "date serial number", which is formatted as a date.

In that case, DATEVALUE returns #VALUE because the value in G4 is numeric, not text.  I suspect, that is not the (intended) cause of the #VALUE result that you see.