Solved how to create nested if function for date and military time

August 31, 2013 at 02:43:02
Specs: Windows 7
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";

See More: how to create nested if function for date and military time

Report •


#1
August 31, 2013 at 07:58:10
✔ Best Answer
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

http://www.skeptic.com/

message edited by mmcconaghy


Report •

#2
September 2, 2013 at 10:40:55
Mike,
Great it works, if you could let me know the logic of "","" for my academic interest.
Thanks, your help greatly appreciated.

Report •

#3
September 2, 2013 at 10:59:14
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

http://www.skeptic.com/


Report •
Related Solutions


Ask Question