How to I test for a blank time entry?

  • Thread starter Thread starter Phil C
  • Start date Start date
P

Phil C

Hi Guys

How do I test for a time-of-day entry being blank? The cell (say A1) is
obviously time formatted (hh:mm) but my usual method for the result cell:
=IF(A1="","",do whatever) doesn't work because the value in A1 is the wrong
type. Have also tried =IF(ISBLANK(A1),"",etc) but that doesn't work either.
When A1 is blank, the result cell displays #VALUE.

For information, the result cell (formatted as General) contains the
following formula: =24*60*IF(A1="","",A1-$C$14). C14 contains another time,
so this is basically a time subtraction problem, for which I need the
decimal result in minutes.

Thanks in advance

Phil
 
A1<>0 will cause problems if the cell A1 contains 00:00 (midnight).

A1="" worked for me, are you sure that's the part of your formula that's
causing the problem?

P
 
Disregard my previous reply re ISBLANK. I missed the fact that you've tried that already.

Looking at your whole formula, it's giving an error because when A1 is empty, your IF formula
returns text, and THEN you multiply "" by 24*60. The multiplication should be part of the FALSE
portion of the IF formula, not outside of it.

The formula should be

=IF(A1="","",24*60*(A1-$C$14))

If you want a 0 result instead of empty text, you could change your original formula to

=24*60*IF(A1="",0,A1-$C$14)

The IF formula always returns a number, so the multiplication will not fail.
 
Phil,

How about testing for zero length?

=24*60*IF(LEN(A1)=0,"",A1-$C$14)

(Not that I could reproduce the error either).

HTH,
Andy
 
Many thanks Myrna

You and Phobos were right. It was the formula that was the problem, not the
'blank condition'. I was looking at the section on subtracting times (and
converting to decimal) on Chip Pearson's web site, and had tried to use a
formula similar to one of his ...obviously inappropriately.

Best wishes, Phil


Myrna Larson said:
Disregard my previous reply re ISBLANK. I missed the fact that you've tried that already.

Looking at your whole formula, it's giving an error because when A1 is empty, your IF formula
returns text, and THEN you multiply "" by 24*60. The multiplication should be part of the FALSE
portion of the IF formula, not outside of it.

The formula should be

=IF(A1="","",24*60*(A1-$C$14))

If you want a 0 result instead of empty text, you could change your original formula to

=24*60*IF(A1="",0,A1-$C$14)

The IF formula always returns a number, so the multiplication will not fail.
Click to expand...
 
Back
Top