Condition formatting for overnight hours worked

[k79mill]

I'm trying to create a chart using conditional formatting that will show the hours that each employee is working. I have a formula for that works for Chris' time but can't figure out a formula that covers both Chris & Richard's schedule since Richard is working overnight. Richard's hours between 2200 hrs & 600 hrs need to be filled in with a color & Chris' hours need to be filled in also to show his hours.

[TABLE="width: 807"]
<tbody>[TR]
[TD][/TD]
[TD="colspan: 2"]SUN [/TD]
[TD="colspan: 2"]MON [/TD]
[TD="colspan: 2"]TUES [/TD]
[TD="colspan: 2"]WED [/TD]
[TD="colspan: 2"]THURS [/TD]
[TD="colspan: 2"]FRI [/TD]
[TD="colspan: 2"]SAT [/TD]
[/TR]
[TR]
[TD][/TD]
[TD="colspan: 2"]28-Jul[/TD]
[TD="colspan: 2"]29-Jul[/TD]
[TD="colspan: 2"]30-Jul[/TD]
[TD="colspan: 2"]31-Jul[/TD]
[TD="colspan: 2"]1-Aug[/TD]
[TD="colspan: 2"]2-Aug[/TD]
[TD="colspan: 2"]3-Aug[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]START[/TD]
[TD]END[/TD]
[TD]START[/TD]
[TD]END[/TD]
[TD]START[/TD]
[TD]END[/TD]
[TD]START[/TD]
[TD]END[/TD]
[TD]START[/TD]
[TD]END[/TD]
[TD]START[/TD]
[TD]END[/TD]
[TD]START[/TD]
[TD]END[/TD]
[/TR]
[TR]
[TD]RICHARD (RB)[/TD]
[TD]2200[/TD]
[TD]600[/TD]
[TD]2200[/TD]
[TD]600[/TD]
[TD]2200[/TD]
[TD]600[/TD]
[TD]2200[/TD]
[TD]600[/TD]
[TD]2200[/TD]
[TD]600[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]CHRIS (CR)[/TD]
[TD][/TD]
[TD][/TD]
[TD]900[/TD]
[TD]1700[/TD]
[TD]900[/TD]
[TD]1700[/TD]
[TD]900[/TD]
[TD]1700[/TD]
[TD]900[/TD]
[TD]1700[/TD]
[TD]900[/TD]
[TD]1700[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 973"]
<tbody>[TR]
[TD]DATE[/TD]
[TD]100[/TD]
[TD]200[/TD]
[TD]300[/TD]
[TD]400[/TD]
[TD]500[/TD]
[TD]600[/TD]
[TD]700[/TD]
[TD]800[/TD]
[TD]900[/TD]
[TD]1000[/TD]
[TD]1100[/TD]
[TD]1200[/TD]
[TD]1300[/TD]
[TD]1400[/TD]
[TD]1500[/TD]
[TD]1600[/TD]
[TD]1700[/TD]
[TD]1800[/TD]
[TD]1900[/TD]
[TD]2000[/TD]
[TD]2100[/TD]
[TD]2200[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[Yongle]

a date is an integer formatted to look like a date( where 1 = 1 Jan 1900 and every day after that is the next number in sequence)
time is a decimal formatted to look like time (0.25 = 6am, 0.5 = noon etc)

so to get time differences correct the numbers to be used in calculations must be the sum of date + time
- the date can be ignored if everything happens on the same day


a useful link
https://exceljet.net/formula/calculate-number-of-hours-between-two-times

 

[k79mill]

a date is an integer formatted to look like a date( where 1 = 1 Jan 1900 and every day after that is the next number in sequence)
time is a decimal formatted to look like time (0.25 = 6am, 0.5 = noon etc)

so to get time differences correct the numbers to be used in calculations must be the sum of date + time
- the date can be ignored if everything happens on the same day


a useful link
https://exceljet.net/formula/calculate-number-of-hours-between-two-times

sorry guess my post wasn't real clear. I know how to get it to figure the total hours. What I'm having trouble with is creating a gantt chart that would show their hours on duty. Ex: R works from 2200 hrs to 0600 hrs so I want the chart filled in with a specific color during those hours. TIA