+ Reply to Thread
Results 1 to 9 of 9

Formula for calculating time delays or early completion then color code it

  1. 12-20-2019, 03:46 PM #1
    LionDragon
    LionDragon is offline
    Registered User
    Join Date
    12-20-2019
    Location
    Houston
    MS-Off Ver
    Office 2010
    Posts
    8

    Formula for calculating time delays or early completion then color code it

    I need to come up with a spreadsheet to calculate differences from a time a task needs to be completed and then color code it depending if it was past a certain time or before a certain time.

    i.e: Task A needs to be done at 12:30pm, and it was completed at 12:43pm. then a cell showing 0:13min in red or the other way around, if task A was completed at 12:21pm, to show in a separate cell (same column as the delayed one) 0:09min in green.

    I tried the IF greater or smaller than: function but the cells will change to one single color or if I copy the formula to the whole column, it will change the values to the first row value.


    THANKS!!!!!
    Reply With QuoteRegister To Reply
  2. 12-20-2019, 03:53 PM #2
    Olly
    Olly is offline
    Forum Expert Olly's Avatar
    Join Date
    09-10-2013
    Location
    Darlington, UK
    MS-Off Ver
    Excel 2016, 2019, 365
    Posts
    6,284

    Re: Formula for calculating time delays or early completion then color code it

    With Target Time in column A, Actual Time in column B, then use this in column C for variance:

    Formula: copy to clipboard 
    Please Login or Register  to view this content.



    Now add two conditional formatting rules, based on formulae:


    Format RED:
    Formula: copy to clipboard 
    Please Login or Register  to view this content.



    Format GREEN:
    Formula: copy to clipboard 
    Please Login or Register  to view this content.
    let Source = #table({"Question","Thread", "User"},{{"Answered","Mark Solved", "Add Reputation"}}) in Source

    If I give you Power Query (Get & Transform Data) code, and you don't know what to do with it, then CLICK HERE

    Walking the tightrope between genius and eejit...
    Reply With QuoteRegister To Reply
  3. 12-20-2019, 04:01 PM #3
    LionDragon
    LionDragon is offline
    Registered User
    Join Date
    12-20-2019
    Location
    Houston
    MS-Off Ver
    Office 2010
    Posts
    8

    Re: Formula for calculating time delays or early completion then color code it

    Now, i see a little hiccup. As you can see in the J column, the first rows are well color coded, after that its not consistent.

    When I did the rules I chose the entire J column as reference.
    Attached Files Attached Files
    Last edited by LionDragon; 12-20-2019 at 04:08 PM. Reason: Correction
    Reply With QuoteRegister To Reply
  4. 12-20-2019, 04:05 PM #4
    Olly
    Olly is offline
    Forum Expert Olly's Avatar
    Join Date
    09-10-2013
    Location
    Darlington, UK
    MS-Off Ver
    Excel 2016, 2019, 365
    Posts
    6,284

    Re: Formula for calculating time delays or early completion then color code it

    Glad to help. If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

    Also, as a relatively new member of the forum, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.
    Reply With QuoteRegister To Reply
  5. 12-20-2019, 04:14 PM #5
    LionDragon
    LionDragon is offline
    Registered User
    Join Date
    12-20-2019
    Location
    Houston
    MS-Off Ver
    Office 2010
    Posts
    8

    Re: Formula for calculating time delays or early completion then color code it

    Thanks Olly, I edited my response as I am working with the spreadsheet as I read your answers.

    To use the same formula and formatting on the entire column I tried a copy/paste of the formula and formatting but gave me a different result. regarding the color coding.
    Reply With QuoteRegister To Reply
  6. 12-20-2019, 04:19 PM #6
    Olly
    Olly is offline
    Forum Expert Olly's Avatar
    Join Date
    09-10-2013
    Location
    Darlington, UK
    MS-Off Ver
    Excel 2016, 2019, 365
    Posts
    6,284

    Re: Formula for calculating time delays or early completion then color code it

    You're a row offset. If you want the formula to apply to D1:D20, then it should be
    Formula: copy to clipboard 
    Please Login or Register  to view this content.
    as the conditional formatting formula...
    Attached Files Attached Files
    Last edited by Olly; 12-20-2019 at 04:25 PM.
    Reply With QuoteRegister To Reply
  7. 12-20-2019, 04:24 PM #7
    LionDragon
    LionDragon is offline
    Registered User
    Join Date
    12-20-2019
    Location
    Houston
    MS-Off Ver
    Office 2010
    Posts
    8

    Re: Formula for calculating time delays or early completion then color code it

    Thanks again, the time calculation is godd now, the color formatting is not consistent, the first 2 rows are good as you can see, then D5 shows red when it should be green.
    Reply With QuoteRegister To Reply
  8. 12-20-2019, 04:26 PM #8
    Olly
    Olly is offline
    Forum Expert Olly's Avatar
    Join Date
    09-10-2013
    Location
    Darlington, UK
    MS-Off Ver
    Excel 2016, 2019, 365
    Posts
    6,284

    Re: Formula for calculating time delays or early completion then color code it

    See my attachment...
    Reply With QuoteRegister To Reply
  9. 12-20-2019, 05:02 PM #9
    LionDragon
    LionDragon is offline
    Registered User
    Join Date
    12-20-2019
    Location
    Houston
    MS-Off Ver
    Office 2010
    Posts
    8

    Re: Formula for calculating time delays or early completion then color code it

    Perfect, thanks Olly, i just saw the attachment, i needed to reload the page.

    Now its working as it should.

    Thank you.
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Help with calculating Early/On Time/Late from Set Time Windows
    By warbopaul in forum Excel General
    Replies: 6
    Last Post: 05-20-2023, 01:07 AM
  2. [SOLVED] Help - Calculating Time Differences (on time, early or late flights)
    By pete_agreatguy in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 04-09-2019, 10:25 AM
  3. Calculating completion date & time per process
    By Wes3574 in forum Excel Formulas & Functions
    Replies: 16
    Last Post: 09-06-2017, 09:16 AM
  4. Formula to Display Notification LATE, EARLY, ON TIME
    By Daryl10 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 04-12-2014, 06:27 PM
  5. [SOLVED] Formula for calculating Late Hrs & Early goings
    By divi123 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 03-07-2014, 05:25 AM
  6. Cycle Time with Delays. Need help on how to represent this in graph form.
    By caseydel in forum Excel Charting & Pivots
    Replies: 0
    Last Post: 05-21-2013, 12:33 PM
  7. Macro which delays for processing time
    By freud1 in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 07-05-2012, 07:04 AM

Bookmarks

Posting Permissions

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1