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

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
12,490
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,175,545
Messages
5,898,051
Members
434,690
Latest member
Shamsuddin M

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