CODEWITHSUNDEEP
exceltimeconditional-formatting
Cecel
Cecel

Reputation: 37

Excel Highlighting current time cell

I'm designing a time schedule on MS Excel (or Google Sheets).
I'd like it to highlight the cell that contains the time which falls within half an hour before the current time.
Is there a way to do that?
What I did is:

A1= now()
A2= now()+time(0,30,0)

Cells A3:A33 contain times I manually typed with an interval of 30 minutes between each, e.g. A3=07:00, A4=07:30, ... , A32= 23:30.
And I applied conditional formatting to cells A3:A33 as follows:

If the value is between A1 and A2, then highlight the cells.

But nothing seems to change.
Any ideas?

Upvotes: 1

Views: 5038

Answers (1)

Axel Richter
Axel Richter

Reputation: 61870

The result of NOW is not only current time but current date plus current time. So if the other cells contains the time only, do using

=TIME(HOUR(NOW()),MINUTE(NOW()),SECOND(NOW())) in A1

and

=A1+TIME(0,30,0) in A2.

Then A1will contain current time only and A2 will contain current time plus 30 minutes.

In Conditional Formatting use:

...between $A$1 and $A$2

Upvotes: 1

Related Questions