At 10:03 18/10/2008 -0400, Bill Drescher wrote:
I have a column of times in the format: hh:mm:ss PM.
I need to do a move a value to a cell that depends on the time.
to move the value in G4 to this cell if the time in B4 is before
9:30 AM, I tried
=IF(B4<"9:30 am";G4;"")
=IF(B4<(9.5/24);G4;"")
with no joy either way.
So, how does one check to see if a time if before a constant time ?
This depends on exactly what you have in your cells. If you have
genuine time values formatted as "HH:MM:SS AM/PM", then your first
suggestion will not work, since you are comparing a time value -
stored as a numeric fraction of a day - with a text value. You need
to convert your text string to a time value using the TIMEVALUE() function:
=IF(B4<TIMEVALUE("9:30 am");G4;"")
But your second suggestion should work: it does for me. (The inner
parentheses are unnecessary but do no harm.) If it doesn't, then I
suspect you may not have proper time values in your cells. Is it
possible that you have text strings instead? The string "09:29:59
AM" is different from a time value which will display the same way
with the appropriate cell format. And you cannot compare such
strings numerically, of course. If you have string values, you need
to convert these to time values before attempting the comparison:
=IF(TIMEVALUE(B4)<TIMEVALUE("9:30 am");G4;"")
Alternatively, you could convert all your text values in the cells to
time values first, also using TIMEVALUE() - possibly pasting the
converted time values back over the original text values.
I trust this helps.
Brian Barker
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
