Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 250
  • Last Modified:

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
0
Steve_Brady
Asked:
Steve_Brady
  • 3
  • 3
  • 2
  • +1
2 Solutions
 
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
 
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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

  • 3
  • 3
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now