Excel 2010 If Then logic - with time

  • Thread starter Thread starter rmscg15
  • Start date Start date
R

rmscg15

I would like to use a time format as the logical test and the statement does not seem to recognize the time format. It converts it to some 1.XXXX number.
Any suggestions?

Thanx,

Richard
 
I would like to use a time format as the logical
test and the statement does not seem to recognize
the time format. It converts it to some 1.XXXX
number.

Any suggestions?
Click to expand...

If you're trying to test the value of a cell containing a time, remember that a time is really a number: a fraction of a day. For example, 0.5 is noon, or 12 hours (12:00) -- half a day. So compare times like numbers.

If you're trying to test if a cell is in the time format or not, take a look at the function
CELL(info_type, [reference])
with info_type="format".

(I have Excel 2010.)
 
MyVeryOwnSelf said:
I would like to use a time format as the logical
test and the statement does not seem to recognize
the time format.
Click to expand...
[....]
If you're trying to test if a cell is in the time format
or not, take a look at the function CELL(info_type, [reference])
with info_type="format".
Click to expand...

Caveat lector: CELL("format",A1) recognizes only some time formats. For
example, for hh:mm:ss, it returns "D8". But it returns "G" for [h]:mm:ss, a
common alternative.

Moreover, CELL is not recalculated if you only change the referenced cell's
format. So you must recalculate the cell that uses CELL manually, in one of
several ways.

IMHO, it is "bad practice" to use the cell's format as a formula condition.
But viable alternatives can only be suggested if/when the "rmscg15" explains
why he/she wants to rely on the cell format in the first place.
 
Back
Top