Condition formatting for overnight hours worked

k79mill

New Member
Joined
Jul 17, 2019
Messages
2
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-Jul29-Jul30-Jul31-Jul1-Aug2-Aug3-Aug
STARTENDSTARTENDSTARTENDSTARTENDSTARTENDSTARTENDSTARTEND
RICHARD (RB)22006002200600220060022006002200600
CHRIS (CR)90017009001700900170090017009001700

<tbody>
</tbody>

DATE1002003004005006007008009001000110012001300140015001600170018001900200021002200

<tbody>
</tbody>
 

Some videos you may like

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
6,557
Office Version
365
Platform
Windows
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
 
Last edited:

k79mill

New Member
Joined
Jul 17, 2019
Messages
2
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
 

Subscribe on YouTube

Watch MrExcel Video

Forum statistics

Threads
1,106,309
Messages
5,510,530
Members
408,794
Latest member
Eddie74

This Week's Hot Topics

  • Turn fraction around
    Hello I need to turn a fraction around, for example I have 1/3 but I need to present as 3/1
  • TIme Clock record reformatting to ???
    Hello All, I'd like some help formatting this (Tbl-A)(Loaded via Power Query) [ATTACH type="full" width="511px" alt="PQdata.png"]22252[/ATTACH]...
  • TextBox Match
    hi, I am having a few issues with my code below, what I need it to do is when they enter a value in textbox8 (QTY) either 1,2 or 3 the 3 textboxes...
  • Using Large function based on Multiple Criteria
    Hello, I can't seem to get a Large formula to work based on two criteria's. I can easily get a oldest value based one value, but I'm struggling...
  • Can you check my code please
    Hi, Im going round in circles with a Compil Error End With Without With Here is the code [CODE=rich] Private Sub...
  • Combining 2 pivot tables into 1 chart
    Hello everyone, My question sounds simple but I do not know the answer. I have 2 pivot tables and 2 charts that go with this. However I want to...
Top