# Thread: Condition formatting for overnight hours worked Thanks: 0 Likes: 0

1. ## Condition formatting for overnight hours worked

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.

 SUN MON TUES WED THURS FRI SAT 28-Jul 29-Jul 30-Jul 31-Jul 1-Aug 2-Aug 3-Aug START END START END START END START END START END START END START END RICHARD (RB) 2200 600 2200 600 2200 600 2200 600 2200 600 CHRIS (CR) 900 1700 900 1700 900 1700 900 1700 900 1700

 DATE 100 200 300 400 500 600 700 800 900 1000 1100 1200 1300 1400 1500 1600 1700 1800 1900 2000 2100 2200

2. ## Re: Condition formatting for overnight hours worked

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

https://exceljet.net/formula/calcula...ween-two-times

3. ## Re: Condition formatting for overnight hours worked

Originally Posted by 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

https://exceljet.net/formula/calcula...ween-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