# Condition formatting for overnight hours worked

#### k79mill

##### New Member
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

<tbody>
</tbody>

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

<tbody>
</tbody>

### Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.

#### Yongle

##### Well-known Member
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/calculate-number-of-hours-between-two-times

Last edited:

#### k79mill

##### New Member
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/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

1,101,914
Messages
5,483,689
Members
407,399
Latest member
Rakeforms

### This Week's Hot Topics

• Finding issue in If elseif else with For each Loop
Finding issue in If elseif else with For each Loop I have tried this below code but i'm getting in Y column filled with W005. Colud you please...
• MsgBox Error
Hi Guys, I have the below error show up when i try and run my macro in File1 but works fine if i copy and paste the same code into file2. [ATTACH...
• CELL FORMAT - IF CONDITION
My Cell Format is [B]""0.00" Cr". [/B]But in the cell, it is showing 123.00 for editing. (123 is entry figure). (Data imported from other...
• Show numbers nearly the same
Is this possible. I have a number that can change very time eg 0.00001234 Then I have a lot of numbers 0.0000001, 0.0000002, 0.00000004...