cell A1 has military time, 04:25:00

condition if the value of time in A1 is between 07:00:00 and 15:00:00, then B1 should display "a"; if the value of time in A1 is between 15:00:00 and 23:00:00, then B1 should display "b";if the value of time in A1 is between 23:00:00 and 07:00:00, then B1 should display "c";

Try this and see how it works: =IF(OR(A1<TIME(7,0,0),A1>=TIME(23,0,0)),"C",IF(AND(A1>=TIME(7,0,0),A1<TIME(15,0,0)),"A",IF(AND(A1>=TIME(15,0,0),A1<TIME(23,0,0)),"B")))

EDIT:

Hers is an updated formula:

=IF(A1="","",IF(OR(A1<TIME(7,0,0),A1>=TIME(23,0,0)),"C",IF(AND(A1>=TIME(7,0,0),A1<TIME(15,0,0)),"A",IF(AND(A1>=TIME(15,0,0),A1<TIME(23,0,0)),"B"))))

If A1 is empty, this will blank the cell.

MIKE

message edited by mmcconaghy

Mike,

Great it works, if you could let me know the logic of "","" for my academic interest.

Thanks, your help greatly appreciated.

let me know the logic of "",""The structure of an IF statement is:

IF( logical_test , value_if_true , value_if_false )

So =IF(A1="","" translates to:

If the Logical_test returns TRUE, then do nothing,

denoted by the null string "" (two double quotes together)So if A1 is equal to nothing or null,

make the cell equal to nothing or blank the cell.What will happen if this test fails, is even though A1

looks blank, there may be a space character or some other Non-printable character in cell A1, and a space is a valid character in Excel.

Something to be aware of.MIKE

Ask Your Question

Weekly Poll