Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 01-31-2021, 01:12 PM
Ashikido Ashikido is offline How to change colors to time values Windows 10 How to change colors to time values Office 2019
Novice
How to change colors to time values
 
Join Date: Dec 2019
Posts: 6
Ashikido is on a distinguished road
Default How to change colors to time values

Hi,
I have a table with time zones, see png.
I would like to format the table so that the local date time would show in green when, for example, it is between 9am and 5pm, and in the red after 5pm until 11.59pm.
How can I do this?
I tried with conditional formatting but for some reason I cannot get it to work.


Thanks
Attached Images
File Type: png 2021-01-31.png (35.1 KB, 11 views)
Reply With Quote
  #2  
Old 01-31-2021, 09:42 PM
Purfleet Purfleet is offline How to change colors to time values Windows 10 How to change colors to time values Office 2019
Expert
 
Join Date: Jun 2020
Location: Essex
Posts: 290
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 add a workbook and not a picture as we just have to waste time recreating somthing that already exists
Reply With Quote
  #3  
Old 02-01-2021, 12:25 AM
ArviLaanemets ArviLaanemets is offline How to change colors to time values Windows 8 How to change colors to time values Office 2016
Expert
 
Join Date: May 2017
Posts: 617
ArviLaanemets is a glorious beacon of lightArviLaanemets is a glorious beacon of lightArviLaanemets is a glorious beacon of lightArviLaanemets is a glorious beacon of lightArviLaanemets is a glorious beacon of light
Default

Something like this?
Attached Files
File Type: xlsx LocalTime.xlsx (11.9 KB, 4 views)
Reply With Quote
  #4  
Old 02-01-2021, 09:14 AM
p45cal p45cal is offline How to change colors to time values Windows 10 How to change colors to time values Office 2019
Expert
 
Join Date: Apr 2014
Posts: 480
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

Make one of the formulae:
=AND(MOD($B2,1)>=9/24,MOD($B2,1)<=17/24)
and the other:
=AND(MOD($B2,1)>17/24,MOD($B2,1)<1)
[actually, that second formula can be simplified to =MOD($B2,1)>17/24, untested]


edit post posting: Oops. I've just noticed the attached file was Arvi's and not the OP's! Sorry. And it would help if I read the question properly! Updated.
Attached Files
File Type: xlsx msofficeforums46427LocalTime.xlsx (12.2 KB, 3 views)
Reply With Quote
  #5  
Old 02-01-2021, 11:29 AM
Ashikido Ashikido is offline How to change colors to time values Windows 10 How to change colors to time values Office 2019
Novice
How to change colors to time values
 
Join Date: Dec 2019
Posts: 6
Ashikido is on a distinguished road
Default

Quote:
Originally Posted by ArviLaanemets View Post
Something like this?

Exactly. How did you manage to do that?

EDIT:
Oh, I see, with a formula.
Reply With Quote
  #6  
Old 02-01-2021, 11:35 AM
Ashikido Ashikido is offline How to change colors to time values Windows 10 How to change colors to time values Office 2019
Novice
How to change colors to time values
 
Join Date: Dec 2019
Posts: 6
Ashikido is on a distinguished road
Default

Quote:
Originally Posted by p45cal View Post
Make one of the formulae:
=AND(MOD($B2,1)>=9/24,MOD($B2,1)<=17/24)
and the other:
=AND(MOD($B2,1)>17/24,MOD($B2,1)<1)
[actually, that second formula can be simplified to =MOD($B2,1)>17/24, untested]


edit post posting: Oops. I've just noticed the attached file was Arvi's and not the OP's! Sorry. And it would help if I read the question properly! Updated.
Thank you, this is useful. i realized that in the conditional formatting section I was picking "format only cells that contain" instead of "Use a formula... ).
Reply With Quote
  #7  
Old 02-01-2021, 11:37 AM
Ashikido Ashikido is offline How to change colors to time values Windows 10 How to change colors to time values Office 2019
Novice
How to change colors to time values
 
Join Date: Dec 2019
Posts: 6
Ashikido is on a distinguished road
Default

Quote:
Originally Posted by Purfleet View Post
Please add a workbook and not a picture as we just have to waste time recreating somthing that already exists
Good advice. I hadn't thought about that.
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Merging 2 different cells containing IF formula & change in cell values based on multiple time frame jay_excel Excel 0 07-29-2017 11:04 PM
Track Change & Reviewer Colors mdarner Word 1 05-27-2015 11:09 AM
Setting specific values to colors macitaa Excel 2 03-26-2015 11:54 AM
How to change colors to time values Change Standard Colors in Powerpoint (not munichone PowerPoint 1 02-27-2015 07:11 AM
How to change colors to time values Unable to change font colors from theme colors choy Word 3 08-01-2012 09:12 PM

Other Forums: Access Forums - Senior Forums

All times are GMT -7. The time now is 07:58 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