Calculate Day and Night Shift based on Time

[imfarhan]

Hi All,
Can some one please correct my formula to calculate the Day or Night shift.
07:00 am to 19:00 (Day)
19:00 > to <07:00am (Night)

=IF(AND(C4>=7, C4<=19),"Day","Night")

Thanks
Farhan

 

[Fluff]

How about

=IF(AND(C4>=TIME(7,0,0),C4<=TIME(19,0,0)),"Day","Night")

or

=IF(AND(C4*24>=7,C4*24<=19),"Day","Night")

 

[MrTomasz]

try:

=IF(AND(HOUR(C4)>=7, HOUR(C4)<=19),"Day","Night")

 

[jasonb75]

2 more, the simplest one first as you already have the times at the top of the sheet.

=IF(AND(C4>=$D$2, C4<=$E$2),"Day","Night")

FYI, times are decimal values, 1 hour is 1/24 days so you were actually looking for 7 and 19 days with your formula instead of hours. Dividing by 24 will covert any integer to hours

=IF(AND(C4>=(7/24), C4<=(19/24)),"Day","Night")

or the reverse can be done by multiplying (as in @Fluff's suggestion).
As you can see from the answers provided, there are many ways to convert to time when needed.

 

[imfarhan]

=IF(AND(C4*24>=7,C4*24<=19),"Day","Night")

Thanks a lot its works

 

[Fluff]

Glad we could help & thanks for the feedback.

 

[imfarhan]

How about

=IF(AND(C4>=TIME(7,0,0),C4<=TIME(19,0,0)),"Day","Night")

or

=IF(AND(C4*24>=7,C4*24<=19),"Day","Night")

Thanks Fluff it works for me

 

[imfarhan]

2 more, the simplest one first as you already have the times at the top of the sheet.

=IF(AND(C4>=$D$2, C4<=$E$2),"Day","Night")

FYI, times are decimal values, 1 hour is 1/24 days so you were actually looking for 7 and 19 days with your formula instead of hours. Dividing by 24 will covert any integer to hours

=IF(AND(C4>=(7/24), C4<=(19/24)),"Day","Night")

or the reverse can be done by multiplying (as in @Fluff's suggestion).
As you can see from the answers provided, there are many ways to convert to time when needed.

Thanks Jason to explain now I understand why Fluff *24 or can be divide by 24 appreciate put extra effort to explain