Time and date related Conditional Formatting question in Excel

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):
 aIf
     • 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
Steve_BradyAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Steve_BradyAuthor Commented:
Please note that the very last question was added as an edit (i.e. early birds may have missed it).
0
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
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
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

gowflowCommented:
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

Open in new window


3) you add in your wokbook open the following code
---------------
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
barry houdiniCommented:
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
barry houdiniCommented:
...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
barry houdiniCommented:
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
Steve_BradyAuthor Commented:
>>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
Steve_BradyAuthor Commented:
Thanks
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.