0

Please forgive me since I don't know where to start with this problem. I have an Excel sheet with multiple columns. Two of the columns are dates in the format of "MM/DD/YYYY HH:MM:SS", column A and column B. I want to highlight when column A is more than 2 hrs ahead of column B.

I click on A2 and tell it to highlight the A2 cell when cell value[dropdown option] is greater than[dropdown option] =B2 screenshot of what I did Then I click on the paintbrush to copy formatting to all column A cells and that works; A* cells that are > B* cells are highlighted. A* cells that aren't > B* cells don't get highlighted.

However, I tried to say =B2+120 (for 120 minutes) or =B2+7200 (for 7200 sec) and it doesn't work. Are there any tips on getting this to work? Am I supposed to convert to epoch and then do the comparison + 2 hrs? I don't want to have to convert to epoch and then do the comparison b/c I have several sheets to do this on. If I can just click on conditional formatting and do what I did in the screenshot and add + 2 hrs in minutes or seconds, it will make it easier/faster to check each sheet.

Thanks in advance for your help.

Improve this question

1 Answer 1

Reset to default
2

In Excel, dates are integers and times are decimals. If you want to add a time value to a date, you can use the Time(hours, minutes, seconds) function.

I don't do Excel for Mac, but in Excel for Windows I'd use a rule with a formula, not one of the prefabricated highlight rules, and the formula would be

=A2>B2+time(0,120,0)
Improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .