#1
|
|||
|
|||
Time Conditional Formatting
Hi,
When I paste time in excell I want that time later than 19:00 it becomes with a different color from other time (earlier than 19:00) Please see the attachment time excel.PNG - Google Drive |
#2
|
||||
|
||||
Highlight cell rules>Greater than>18:59
I'm not sure but your problem statement is not consistent with the highlighted value in the image. 19:00 is equal to 19:00, not later. BTW, the ever helpful members of this forum are very particular with how we express our need for help in any Excel problem. |
#3
|
|||
|
|||
Hi Marcia,
I tried before as you suggested to Highlight cell rules>Greater than>18:59. But it works only in number format and not in time format! And ok let's say later than 18:59 |
#4
|
|||
|
|||
please upload a file and not pictures - i cant do anything with a picture!
|
#5
|
|||
|
|||
Time is just a fraction of a day, so 19:00 is 0.7916667 of a day.
then conditional format to that number (roundings may casue a problem) |
#6
|
||||
|
||||
Posting a sample sheet ( not a pic) saves time for everyone...
|
#7
|
||||
|
||||
to save you working out the 0.7916667 you can use:
=19/24 in the conditional formatting rule. |
#8
|
|||
|
|||
Hi, I uploaded a sheet.
|
#9
|
||||
|
||||
Only cells A5:A7 in your file are proper times, the rest are text, looking like times/numbers. So the conditional formatting doesn't work.
If you select cell A1, press F2, then press Enter, Excel will convert that cell to a number and move to the next cell (it's just you re-entering the cell to edit it and pressing Enter without changing anything). You could repeat the key presses all the way down the column (tedious) butyou can also select all the cells and type: Code:
Selection.Value = Selection.Value Better is to perhaps to change how the data gets there in the first place; how are you getting that data there at the moment? |
#10
|
||||
|
||||
I do not have my laptop with me at the moment but what I do to change text to numbers is to select a blank cell>copy>select the range to be converted>paste special>add.
|
#11
|
||||
|
||||
First remove your manual alignment. You now see that some contents are left aligned, they are text
To change the text time values to real time values, just select the range - Data Text To columns - Finish Now all should be " right aligned" and thus are numbers "Formatting " is just how XL lets you see the values. The underlying values are still numbers, so =1/24 ( which is one hour) could be shown as 01:00:00, but the underlying value still is 0.0416667 |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Conditional Formatting | RocketRon | Excel Programming | 2 | 11-25-2019 08:36 AM |
Conditional formatting that ignores other formatting rules | info_guy2 | Excel | 1 | 07-03-2014 10:07 AM |
Conditional Formatting | teza2k06 | Excel | 3 | 01-19-2014 02:34 AM |
Conditional formatting with AND, OR | Lucky | Excel | 2 | 10-03-2011 11:41 PM |
help with conditional formatting | Snvlsfoal | Excel | 3 | 07-03-2011 11:55 PM |