+ Reply to Thread
Results 1 to 7 of 7

conditional formatting if time exceeded

  1. #1
    Registered User
    Join Date
    02-16-2013
    Location
    Folsom, CA
    MS-Off Ver
    Excel 2010
    Posts
    47

    conditional formatting if time exceeded

    I am working on a sheet that adds up time entries. I'd like the cell in which the total time appears to fill in red if the time exceeds 80 minutes (1:20:00). So in my example, the total appears in cell E20. I tried using conditional formatting to state that the fill would turn red if E20>1:20:00, but that didn't work. I tried several other options as well, but nothing has worked.
    Attached Files Attached Files

  2. #2
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Re: conditional formatting if time exceeded

    try in CF: =E23>TIME(1,20,0)
    Last edited by sandy666; 09-25-2017 at 02:51 PM. Reason: file added

  3. #3
    Forum Guru benishiryo's Avatar
    Join Date
    03-25-2011
    Location
    Singapore
    MS-Off Ver
    Excel 2013
    Posts
    5,147

    Re: conditional formatting if time exceeded

    hi dzugan. and the reason why your formula couldn't work is because date and time has underlying values that don't match its presentation. Excel recognises dates as integers & time as decimals. to verify, type in 1jan2013 in A1. and 12 pm in B1. Format both cells to General. you will see that A1 is 41275 (meaning 41,275th day from 1 Jan 1900) & B1 is 0.5 (1/2 a day).

    so if you use E20>1:20:00, Excel could not recognize what 1:20:00 mean. to Excel, 1 hr and 20 mins is 0.0555555555555556. but rather than putting it as such, you can show it in terms of how sandy is showing it in TIME(1,20,0). the same goes for date. you can never use E20>1/1/2017 but E20>DATE(2017,1,1)

    Thanks, if you have clicked on the * and added our rep.

    If you're satisfied with the answer, click Thread Tools above your first post, select "Mark your thread as Solved".

    "Contentment is not the fulfillment of what you want, but the realization of what you already have."


    Tips & Tutorials I Compiled | How to Get Quick & Good Answers

  4. #4
    Registered User
    Join Date
    02-16-2013
    Location
    Folsom, CA
    MS-Off Ver
    Excel 2010
    Posts
    47

    Re: conditional formatting if time exceeded

    thank you.

  5. #5
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Re: conditional formatting if time exceeded

    If that takes care of your original question, & to say Thanks, please click on Add Reputtion (bottom left corner of the post of the person(s) who helped you)
    then select Thread Tools from the menu (top right corner of your thread) and mark this thread as SOLVED.
    If you did it - ignore it.
    Thank you.

  6. #6
    Registered User
    Join Date
    02-16-2013
    Location
    Folsom, CA
    MS-Off Ver
    Excel 2010
    Posts
    47

    Re: conditional formatting if time exceeded

    thanks for the additional info. I like to understand why the answer works, because it invariably helps me figure out other formulas down the line

  7. #7
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Re: conditional formatting if time exceeded

    1st: your CF formula doesn't work because it looks like: ="D23>1:20:00" and this is a text. No references to anything.
    2nd: post #3
    3rd: you can read more: here
    Last edited by sandy666; 09-27-2017 at 12:22 AM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Using Conditional Formatting with time
    By MatthewIJClark in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 09-12-2017, 06:49 AM
  2. [SOLVED] Conditional formatting with time
    By dunnobe in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 08-30-2017, 05:27 AM
  3. Replies: 8
    Last Post: 09-18-2014, 03:07 PM
  4. Replies: 4
    Last Post: 01-11-2012, 07:59 PM
  5. Countif to find if a time has been exceeded
    By inayat in forum Excel General
    Replies: 3
    Last Post: 09-05-2011, 04:00 AM
  6. Row to change colour if time is exceeded.
    By lpratt in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 01-13-2011, 03:59 AM
  7. Share:Fix a time not to be exceeded
    By 007007007 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 01-06-2006, 09:22 PM

Tags for this Thread

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1