Highlight a timestamp if within a time of another timestamp

Paul365

New Member
Joined
Oct 29, 2021
Messages
8
Office Version
  1. 2016
Platform
  1. Windows
  2. Mobile
Hello,

I am trying to conditional format a spread sheet with time stamps on it. I am looking to have...
Start time 'Cell A1'and in Cell A2 and A3 some other time stamps with different times.
I would like all cells except A1 to highlight different colors if they are within a certain time of A1
example: A1 12:05 PM
- If the other cells times are under 1 hour of A1, I would like them to turn green,
- If the other cells are between 1 and 2 hours of A1 to turn Orange
- If the other cells are 2+ hours of A1 to turn Red.

I have looked up so many website and all I get is formula's with dates or the current time, not a previous time.

If anyone can help would be much appreciated

Thanks
 

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
12,496
Welcome to the MrExcel forum!

Start by formatting A2 and A3 with a green fill color. Then set up your Conditional Formatting like this:

Book1
A
112:05 PM
210:55 AM
32:24 PM
Sheet4
Cells with Conditional Formatting
CellConditionCell FormatStop If True
A2:A3Expression=ABS($A$1-A2)>2/24textNO
A2:A3Expression=ABS($A$1-A2)>1/24textNO
 
Solution

Paul365

New Member
Joined
Oct 29, 2021
Messages
8
Office Version
  1. 2016
Platform
  1. Windows
  2. Mobile
Welcome to the MrExcel forum!

Start by formatting A2 and A3 with a green fill color. Then set up your Conditional Formatting like this:

Book1
A
112:05 PM
210:55 AM
32:24 PM
Sheet4
Cells with Conditional Formatting
CellConditionCell FormatStop If True
A2:A3Expression=ABS($A$1-A2)>2/24textNO
A2:A3Expression=ABS($A$1-A2)>1/24textNO
This is perfect, Thankyou so much
 

Forum statistics

Threads
1,176,078
Messages
5,901,271
Members
434,884
Latest member
ares0027

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top