+ Reply to Thread
Results 1 to 9 of 9

Conditional formatting with time

  1. #1
    Forum Contributor
    Join Date
    12-08-2012
    Location
    USA
    MS-Off Ver
    Excel 365
    Posts
    173

    Conditional formatting with time

    is there any way to make a conditional format for cells bassed off what time of the day they are entered in the cell?

    ie 00:00 - 01:00 is one color

    ect ect


    any ideas?

  2. #2
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Office 365
    Posts
    10,316

    Re: Conditional formatting with time

    Time in Excel is a number, that is, a day is equal to 1 with all time in between being a fraction.

    http://www.cpearson.com/excel/datetime.htm

    So let's say you want to check to see if a time is between 6:00 AM and 7:00 AM

    =AND($A$1>=0.25,$A$1<=0.29)

    Conditional Formatting
    • Highlight applicable range >> A1
    • Home Tab >> Styles >> Conditional Formatting
    • New Rule >> Use a formula to determine which cells to format
    • Edit the Rule Description: Format values where this formula is true: =AND($A$1>=0.25,$A$1<=0.29)
    • Format… [Number, Font, Border, Fill]
    • OK >> OK

    Place a time in a cell and then select Ctrl + Shift + 1 which will show you the underlying decimal.
    HTH
    Regards, Jeff

  3. #3
    Forum Contributor
    Join Date
    12-08-2012
    Location
    USA
    MS-Off Ver
    Excel 365
    Posts
    173

    Re: Conditional formatting with time

    ok maby this will work....dates are not enterd into the cell..

    say I add data at 2pm to a cell I want it to get formatted one way..and then in another cell I add data when its 3pm..i want it to be formatted differently

  4. #4
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Office 365
    Posts
    10,316

    Re: Conditional formatting with time

    How about a sample workbook for illustration purposes (before and after)?

  5. #5
    Forum Contributor
    Join Date
    12-08-2012
    Location
    USA
    MS-Off Ver
    Excel 365
    Posts
    173

    Re: Conditional formatting with time

    ok ill try that


    we I dunno hwo to illustrate it

    hows this

    A1 = 50000
    A2 = 23000
    A3 = 1500
    A4 = 100

    Data in A1 was entered at 0600
    Data in A2 was entered at 1000
    Data in A3 was entered at 1500
    Data in A4 was entered at 2100

    each time period (ie 1 hr) a different condtional formatting would be applied based off when it was added

  6. #6
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Office 365
    Posts
    10,316

    Re: Conditional formatting with time

    Okay that is making it a little clearer, but for this you will need VBA.

    Do you want to go the VBA route?

  7. #7
    Forum Contributor
    Join Date
    12-08-2012
    Location
    USA
    MS-Off Ver
    Excel 365
    Posts
    173

    Re: Conditional formatting with time

    ya I don't mind the VBA route...I understand vba a bit

  8. #8
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Office 365
    Posts
    10,316

    Re: Conditional formatting with time

    All credit goes to Norie, he proposed the code below and after testing, it works great...

    Just add the other cases for the other times. In military time... (20 = 8:00 PM)

    Please Login or Register  to view this content.
    For additional colors...

    http://dmcritchie.mvps.org/excel/colors.htm

  9. #9
    Forum Contributor
    Join Date
    12-08-2012
    Location
    USA
    MS-Off Ver
    Excel 365
    Posts
    173

    Re: Conditional formatting with time

    thx so much

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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