+ Reply to Thread
Results 1 to 12 of 12

Conditional Formatting using hours/time

  1. #1
    Registered User
    Join Date
    03-26-2009
    Location
    Orlando, Florida
    MS-Off Ver
    Excel 2002
    Posts
    10

    Conditional Formatting using hours/time

    I'm trying to use conditional formatting to highlight phone calls that came in between certain hours.

    The call times are in the custom format h:mm, although it could easily be changed to an Excel time format. I'm using Excel 2002. I want the cell to have a different fill color if it falls within particular time frames.

    Example: If the call came in between 22:00 and 23:59 color is light green.
    If the call came in between 23:59 and 08:00 the color is yellow.

    Example spreadsheet is attached.

    Thanks in advance.
    Attached Files Attached Files
    Last edited by dwtaxguy; 07-22-2009 at 02:43 PM. Reason: changing title to solved

  2. #2
    Forum Expert Palmetto's Avatar
    Join Date
    04-04-2007
    Location
    South Eastern, USA
    MS-Off Ver
    XP, 2007, 2010
    Posts
    3,978

    Re: Conditional Formatting using hours/time

    Are those the only two conditions? Your version of Excel is limited to threee condition, but with a bit of VBA you can have many more.

  3. #3
    Registered User
    Join Date
    07-21-2009
    Location
    New York
    MS-Off Ver
    Excel 2003
    Posts
    5

    Re: Conditional Formatting using hours/time

    You must first make sure that there are spaces between the numerical time and the AM/PM of column "C". This converts them to a time and not text.

    The conditional formatting is:

    Condition 1
    [Cell Value Is] [between] [=time(0,0,0)] [=time(8,0,0]
    Format to yellow


    Condition 2
    [Cell Value Is] [between] [=time(22,0,0)] [=time(23,59,59]
    Format to green


    I've attached the file also.
    Attached Files Attached Files

  4. #4
    Registered User
    Join Date
    03-26-2009
    Location
    Orlando, Florida
    MS-Off Ver
    Excel 2002
    Posts
    10

    Re: Conditional Formatting using hours/time

    Palmetto: Yes, those are the only conditions. I want to be able to highlight the cells that have calls coming in between certain hours.

    NewMisslesylo: I can get your formula to work, however my spreadsheet has the hours column formatted without the space between the HH:MM and the AM/PM. Rather than manually input a space in every entry, I tried using the RIGHT, LEFT & LEN functions to format the number the way I want it. I was able to get the format right, but unable to get the conditional formatting to work on the new format. Any ideas? Attaching an updated spreadsheet.

    Thanks!

  5. #5
    Forum Expert Palmetto's Avatar
    Join Date
    04-04-2007
    Location
    South Eastern, USA
    MS-Off Ver
    XP, 2007, 2010
    Posts
    3,978

    Re: Conditional Formatting using hours/time

    Follow these steps to convert your time format to what newmissilesylo gave you.

    Select the column range which holds the dates and update the custom format to: h:mm AM/PM

    With the column range still selected:
    1. Press Ctrl+F keys - brings up the Find Dialog
    2. Click on the "Replace" tab
    3. In the "Find What" input box type: PM
    4. In the "Replace With" input box type: _PM, where "_" is a space and not an underscore character
    5. Click "Replace All" button

    Repeat the above steps for AM. Close the Find dialog.

    Next, apply the conditional formats newmissilesylo provided. You should be good-to-go.

  6. #6
    Registered User
    Join Date
    03-26-2009
    Location
    Orlando, Florida
    MS-Off Ver
    Excel 2002
    Posts
    10

    Re: Conditional Formatting using hours/time

    Palmetto,


    See the attachment.

    Thanks!
    Attached Files Attached Files

  7. #7
    Forum Expert Palmetto's Avatar
    Join Date
    04-04-2007
    Location
    South Eastern, USA
    MS-Off Ver
    XP, 2007, 2010
    Posts
    3,978

    Re: Conditional Formatting using hours/time

    Tried every trick I know to get your times converted over to valid format, but was not successful. Perhaps someone esle knows of a way to force Excel to convert the text into true time values.

    In the end, I copied and pasted the data to a text file then used the Data Import feature, using the TAB and SPACE delimiters.

    This caused the AM/PM to split into its own column, but Excel recogized the time as valid.

    If you can live with data in this configuration and don't mind going through the steps I mentioned you can still do the CF using these formulas:

    =AND(D2>=TIME(0,0,0),D2<=TIME(7,59,59),E2="AM")
    =AND(D2>=TIME(10,0,0),D2<=TIME(11,59,59),E2="PM")


    See Attached.

  8. #8
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    MS 2007, 2010
    Posts
    12,202

    Re: Conditional Formatting using hours/time

    I formatted Column F to time (your choice e.g. hh:mm AM/PM) and put this formula dragged down
    Please Login or Register  to view this content.
    That converts it to real time.
    ChemistB
    My 2

    substitute commas with semi-colons if your region settings requires
    Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
    If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)

    Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble

  9. #9
    Registered User
    Join Date
    04-08-2014
    Location
    Las Vegas, NV
    MS-Off Ver
    Excel for Mac 2011 Ver 14
    Posts
    1

    Re: Conditional Formatting using hours/time

    I have a similar challenge I need some of you gurus out there to please help me with. I am monitoring performances at a trade show, & have tried to format the cell backgrounds to change color according to their status. As the time for the upcoming performance approaches, say 5 minutes, I want the cell background to change from white to yellow. As the performance time occurs, I want the cell background to turn green. After the performance concludes, I want the cell background to turn red. I'm attaching a file of my feeble attempt. The colors have been manually changed, but I want them to automatically change through time. PLEASE HELP.CANON NAB Sched.xlsx

  10. #10
    Forum Moderator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    2013, 2016
    Posts
    12,398

    Re: Conditional Formatting using hours/time

    scooparama, unfortunately your post does not comply with Rule 2 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.

    If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.

    Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.
    Jeff

    Making the world a better place one fret at a time | | || || || || | |:| | || ||

    If someone helped you, please click on the star icon at the bottom of their post
    If your problem is solved, please go to Thread Tools and select Mark This Thread Solved
    Don't attach a screenshot--just attach your Excel file! It's easier and will let us experiment with your data, formulas, and code.

  11. #11
    Registered User
    Join Date
    06-26-2017
    Location
    None of Bussiness
    MS-Off Ver
    MS 2010
    Posts
    1

    Re: Conditional Formatting using hours/time

    I confused on time How i set a color on cell for time turn on color that auto change color.

    I explain you this

    you see to yellow at 11:30 on clock now at 10:30 am then later 11 am it change orange then last at 11:30 that
    changed red "warning" on clock now at 11:30 AM

    I figure to set conditional formatting.

    Can you explain me about the conditional formatting? I show you

    1st Time to Plan Reminder to pick up a mail at Post Office
    1st 2nd 3rd 4th
    6:20 AM 11:30 AM 2:15 PM 4:45 PM


    explain
    10:30 AM


    2nd Time to Plan Reminder to pick up a mail at Post Office
    1st 2nd 3rd 4th
    6:20 AM 11:30 AM 2:15 PM 4:45 PM


    explain
    11:00 AM



    3rd Time to Plan Reminder to pick up a mail at Post Office
    1st 2nd 3rd 4th
    6:20 AM 11:30 AM 2:15 PM 4:45 PM


    explain
    11:30 AM
    Attached Files Attached Files

  12. #12
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    44,085

    Re: Conditional Formatting using hours/time

    MagicOwl welcome to the forum

    Unfortunately your post does not comply with Rule 2 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.

    If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.

    Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.

    Also, your location is actually somewhat of your business, so please change that (kind of a rude comment to make anyway, from someone looking for help??) Some countries have different regional settings to other countries, and excel formula structures could change, based on those regional settings, so we need at least your country, so we can take that into account.

    Thanks for the understanding
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think some-1 helped you

    Regards
    Ford

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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