Solved

# Time and date related Conditional Formatting question in Excel

Posted on 2011-10-04
195 Views
Hello,

In an Excel (2010) spreadsheet, suppose that:
• A1 displays the current date & time (assume that it automatically updates every minute), and
• B1:B35 displays times beginning with 7:00 AM in B1 and moving down, every 30 minutes thereafter,
as shown in the following screenshot (note that rows 8-30 are hidden -- red arrow):
If
• turn on = cell colored yellow
• turn off = no cell color (i.e. white or whatever the spreadsheet background color is)
what Conditional Formatting (CF) formula would cause a given cell in column B to turn on as soon as the time in A1 reached the time shown in the cell and turn off 30 minutes later when the next cell in the column turns on?

Also,
• What formatting should be in A1 and column B so that the CF will work correctly day after day?
• While the CF is being applied, should B1:B35 be selected or only B1 after which it is copy/pasted into B2:B35?
• What formula would be entered in C1:C35 so that the (empty) column C cell would turn on & off with its column B partner?

Thanks
0

Author Comment

Please note that the very last question was added as an edit (i.e. early birds may have missed it).
0

LVL 50

Accepted Solution

Hello,

make sure that column B holds only time values, i.e. numbers below 1 except for  B35, which should be a 1. Format as time (but that's just decoration).

Then select B1 to C35 and enter this conditional formula

=AND(MOD(\$A\$1,1)>=\$B1,MOD(\$A\$1,1)<\$B2)

See attached.

cheers, teylyn
Book1.xlsx
0

LVL 50

Assisted Solution

to flesh it out:

• What formatting should be in A1 and column B so that the CF will work correctly day after day?
A1 needs to have a valid date/time value, for example =Now(), column B must have only time values (if formatted as General, then the numbers should be smaller than 1.

• While the CF is being applied, should B1:B35 be selected or only B1 after which it is copy/pasted into B2:B35?
Whatever you prefer. The crucial thing is the referencing of the conditional formula.

• What formula would be entered in C1:C35 so that the (empty) column C cell would turn on & off with its column B partner?
The one formula as mentioned above can be applied to column C as well.

You can either set up the CF in B1 and then copy the format down and across, or you can select B1:C35 in one go and enter the formula.

cheers, teylyn
0

LVL 29

Expert Comment

As usual Teylyn's answers not only grat but with minute details !!!

To complement Teylyn's answer and to make the workbook active, as presume you need the showing of the time not only when workbook is opened but all along the workbook beeing active and even when opened and idle. I suggest the following:

1) You change your workbbok from simple no macro .xlsx to a macro .xlsm.
2) you create a Module in which you add the below code

``````Global RunWhen

Sub StartTimer()
' run it every 1min
RunWhen = TimeSerial(Hour(Now), Minute(Now) + 1, 0)
Application.OnTime EarliestTime:=RunWhen, Procedure:="Calculate", Schedule:=True
End Sub

Sub Calculate()
ActiveSheet.Calculate
StartTimer
End Sub
``````

---------------
Private Sub Workbook_Open()
StartTimer
End Sub
----------------

4) you add also in your sheet Worksheet_SelectionChange event the below code
----------------
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
ActiveSheet.Calculate
End Sub
-----------------

5) Save exit the workbook and try it.

What this will do is refresh the workbook every minute and run Calculate as without it when the time changes pass 30min it will not change color unless you manually trigger calculate in the worksheet. if your worksheet is opened for say 2 hours and you do not do any calculation the time will show the old value whereas with this code even if the workshhet is inactive for as long as you want the time will indicate correctly. even is you simple move the cell selection it will update the time.

Hope this will be use\ful for you as feel you need the coloring (which is a very smart idea to be used in a clendar type where you would hv your appointment and somthing indicating the actual time !! that's how I see it.

PS for your easy refrence I have incorporated the code in Teylyn's attached file for you to see it better. For sure all the credit goes to Teylyn as my suggestion was not in the scoope of the question but felt it might be useful to you.

Enjoy
gowflow
TimeChange-Formating.xlsm
0

LVL 50

Expert Comment

Just one small question, Steve. Do you expect B35 and C35 to ever be coloured yellow - or don't you care at that time of day?

regards, barry
0

LVL 50

Expert Comment

...if you do want row 35 to be highlighted when the time is between midnight and 07:00 you can do this:

Make sure B35 = 0 not 1 as per teylyns setup. Select B35:C35 and apply this formula in conditional formatting

=\$B1=MAX(IF(\$B\$1:\$B\$35<=MOD(\$A\$1,1),\$B\$1:\$B\$35))

format as required

See attached, A1 randomly selects a date/time in the next 24 hours - press F9 to re-generate so you can check how the formatting works - For "real" use A1 should revert to =NOW()

regards, barry
27380541.xlsx
0

LVL 50

Expert Comment

Note: I believe there is a "bug" in Excel 2007 which affects conditional formatting based on formulas that would need to be "array-entered" if placed in a worksheet cell. When I re-open the workbook after closing I have to go into the conditional formatting before it works correctly (otherwise it seems to erroneously format 11:30 PM when it shouldn't). It shouldn't affect you if opened in Excel 2010 (although I can't check that right now).

If it's an issue then you can change to this formula

=\$B1=MAX(INDEX((\$B\$1:\$B\$35<=MOD(\$A\$1,1))*\$B\$1:\$B\$35,0))

regards, barry
0

Author Comment

>>barryhoudini:
...Do you expect B35 and C35 to ever be coloured yellow - or don't you care at that time of day?

The latter, Barry -- but your comments are always welcome and appreciated!
0

Author Closing Comment

Thanks
0

## Featured Post

### Suggested Solutions

Drop Down List with Unique/Distinct Values (Part II - ComboBox or ListBox and Data Validation List Bonus!) David Miller (dlmille) Intro This article focuses on delivering unique, sorted lists to list objects (e.g., ComboBox, ListBox) and Dat…
Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.