Results 1 to 3 of 3

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

  1. #1
    New Member
    Join Date
    Jul 2019
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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. #2
    Board Regular
    Join Date
    Mar 2015
    Posts
    3,908
    Post Thanks / Like
    Mentioned
    72 Post(s)
    Tagged
    7 Thread(s)

    Default 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


    a useful link
    https://exceljet.net/formula/calcula...ween-two-times
    Last edited by Yongle; Jul 17th, 2019 at 05:57 PM.

  3. #3
    New Member
    Join Date
    Jul 2019
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Condition formatting for overnight hours worked

    Quote Originally Posted by Yongle View Post
    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/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

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •