Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 03-09-2021, 02:55 AM
emvella emvella is offline Time Conditional Formatting Windows XP Time Conditional Formatting Office 2007
Novice
Time Conditional Formatting
 
Join Date: Sep 2009
Posts: 10
emvella is on a distinguished road
Default 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
Reply With Quote
  #2  
Old 03-09-2021, 06:38 AM
Marcia's Avatar
Marcia Marcia is offline Time Conditional Formatting Windows 7 32bit Time Conditional Formatting Office 2013
Expert
 
Join Date: May 2018
Location: Philippines
Posts: 364
Marcia has a spectacular aura aboutMarcia has a spectacular aura aboutMarcia has a spectacular aura about
Default

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.
Reply With Quote
  #3  
Old 03-09-2021, 07:16 AM
emvella emvella is offline Time Conditional Formatting Windows XP Time Conditional Formatting Office 2007
Novice
Time Conditional Formatting
 
Join Date: Sep 2009
Posts: 10
emvella is on a distinguished road
Default

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
Reply With Quote
  #4  
Old 03-09-2021, 07:34 AM
Purfleet Purfleet is offline Time Conditional Formatting Windows 10 Time Conditional Formatting Office 2019
Expert
 
Join Date: Jun 2020
Location: Essex
Posts: 263
Purfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to behold
Default

please upload a file and not pictures - i cant do anything with a picture!
Reply With Quote
  #5  
Old 03-09-2021, 07:40 AM
Purfleet Purfleet is offline Time Conditional Formatting Windows 10 Time Conditional Formatting Office 2019
Expert
 
Join Date: Jun 2020
Location: Essex
Posts: 263
Purfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to behold
Default

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)
Attached Files
File Type: xlsx Time Conditional Formatting_purfleet.xlsx (10.4 KB, 2 views)
Reply With Quote
  #6  
Old 03-09-2021, 07:58 AM
Pecoflyer's Avatar
Pecoflyer Pecoflyer is offline Time Conditional Formatting Windows 7 64bit Time Conditional Formatting Office 2010
Expert
 
Join Date: Nov 2011
Location: Brussels Belgium
Posts: 2,456
Pecoflyer is a splendid one to beholdPecoflyer is a splendid one to beholdPecoflyer is a splendid one to beholdPecoflyer is a splendid one to beholdPecoflyer is a splendid one to beholdPecoflyer is a splendid one to behold
Default

Posting a sample sheet ( not a pic) saves time for everyone...
Reply With Quote
  #7  
Old 03-09-2021, 08:10 AM
p45cal p45cal is offline Time Conditional Formatting Windows 10 Time Conditional Formatting Office 2019
Expert
 
Join Date: Apr 2014
Posts: 478
p45cal is a splendid one to beholdp45cal is a splendid one to beholdp45cal is a splendid one to beholdp45cal is a splendid one to beholdp45cal is a splendid one to beholdp45cal is a splendid one to behold
Default

to save you working out the 0.7916667 you can use:
=19/24
in the conditional formatting rule.
Attached Images
File Type: png 2021-03-09_151135.png (7.9 KB, 10 views)
Reply With Quote
  #8  
Old 03-09-2021, 09:05 AM
emvella emvella is offline Time Conditional Formatting Windows XP Time Conditional Formatting Office 2007
Novice
Time Conditional Formatting
 
Join Date: Sep 2009
Posts: 10
emvella is on a distinguished road
Default

Hi, I uploaded a sheet.
Attached Files
File Type: xls TIME.xls (73.0 KB, 3 views)
Reply With Quote
  #9  
Old 03-09-2021, 09:22 AM
p45cal p45cal is offline Time Conditional Formatting Windows 10 Time Conditional Formatting Office 2019
Expert
 
Join Date: Apr 2014
Posts: 478
p45cal is a splendid one to beholdp45cal is a splendid one to beholdp45cal is a splendid one to beholdp45cal is a splendid one to beholdp45cal is a splendid one to beholdp45cal is a splendid one to behold
Default

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
and press Enter in the Immediate pane ofthe VBE to do it en masse.


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?
Reply With Quote
  #10  
Old 03-10-2021, 05:19 AM
Marcia's Avatar
Marcia Marcia is offline Time Conditional Formatting Windows 7 32bit Time Conditional Formatting Office 2013
Expert
 
Join Date: May 2018
Location: Philippines
Posts: 364
Marcia has a spectacular aura aboutMarcia has a spectacular aura aboutMarcia has a spectacular aura about
Default

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.
Reply With Quote
  #11  
Old 03-11-2021, 08:59 AM
Pecoflyer's Avatar
Pecoflyer Pecoflyer is offline Time Conditional Formatting Windows 7 64bit Time Conditional Formatting Office 2010
Expert
 
Join Date: Nov 2011
Location: Brussels Belgium
Posts: 2,456
Pecoflyer is a splendid one to beholdPecoflyer is a splendid one to beholdPecoflyer is a splendid one to beholdPecoflyer is a splendid one to beholdPecoflyer is a splendid one to beholdPecoflyer is a splendid one to behold
Default

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
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Conditional Formatting RocketRon Excel Programming 2 11-25-2019 08:36 AM
Time Conditional Formatting 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
Time Conditional Formatting Conditional formatting with AND, OR Lucky Excel 2 10-03-2011 11:41 PM
Time Conditional Formatting help with conditional formatting Snvlsfoal Excel 3 07-03-2011 11:55 PM

Other Forums: Access Forums - Senior Forums

All times are GMT -7. The time now is 05:22 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2021, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2021 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft