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.

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.

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!

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.

Palmetto,


See the attachment.

Thanks!

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.

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

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