They need to define the shifts something like this:

7:00 AM to 6:59 PM = day shift
7:00 PM to 6:59 AM = night shift

--
Biff
Microsoft Excel MVP


"OssieMac" wrote in message
...
Hello John,

Like Biff I was also confused by whether you want 7PM and 7AM in the night
or day shifts.

The interesting part is that it is difficult to compare actual times
because
you get a difference in the actual numeric values due to the decimal place
inaccuracy so I have converted the times to text values on the 24hr clock
basis. The formula should work for any date.

the following formula includes 7am as day and 7pm as night.
=IF(AND(TEXT(F12-INT(F12),"hh:mm:ss")="07:00:00",TEXT(F12-INT(F12),"hh:mm:ss")<"19:00:00"),"Day","Night")

the following is 7am night and 7pm day.
=IF(AND(TEXT(F12-INT(F12),"hh:mm:ss")"07:00:00",TEXT(F12-INT(F12),"hh:mm:ss")<="19:00:00"),"Day","Night")

the following is both 7am and 7pm night
=IF(AND(TEXT(F12-INT(F12),"hh:mm:ss")"07:00:00",TEXT(F12-INT(F12),"hh:mm:ss")<="19:00:00"),"Day","Night")

following is both 7am and 7pm is day
=IF(AND(TEXT(F12-INT(F12),"hh:mm:ss")="07:00:00",TEXT(F12-INT(F12),"hh:mm:ss")<="19:00:00"),"Day","Night")



--
Regards,

OssieMac