Format Column based on time slot and current time

[triumph]

I am struggling to get a column to be formatted based on the date and time. My sheet is a planner sheet, broken down into 3 hr segments. I want to highlight the column with red borders applicable to the time the document is opened. I have NOW() in A1, F2 is 04/09/2019 00:00:00, F3=F2+0.125. I would like to highlight from row 1 to row 87.

I have tried =NOW() - - AND(<=,=>) - - IF all in several iterations, I am now stumped. Any help appreciated.

 

[jasonb75]

You will not get it to work for time opened with a formula. The NOW() function is volatile, so it will change to the current time whenever the sheet is recalculated.

If you want it fixed to the time that the file is opened then I would suggest creating a timestamp with a workbook open event (vba / macro), then using that as a reference point. Is that something that you would be able to use?

 

[jasonb75]

Try this code in the vba workbook module,

Private Sub Workbook_Open()
ActiveWorkbook.Names.Add Name:="TimeNow", RefersToR1C1:=[CEILING(NOW(),1/8)]
End Sub

which will give you a reference time when the workbook is opened rounded up to the nearest 3 hour point (0:00, 03:00, 06:00, etc.).

You can then use something like =F3=TimeNow to compare the times in column F. Note that as it stands, this will compare date and time, not just time. If the times in the sheet are only time (no date) then the INT function will need to be incorporated as well.

 

[triumph]

You will not get it to work for time opened with a formula. The NOW() function is volatile, so it will change to the current time whenever the sheet is recalculated.

If you want it fixed to the time that the file is opened then I would suggest creating a timestamp with a workbook open event (vba / macro), then using that as a reference point. Is that something that you would be able to use?

Not sure I need to go that deep. The time doesn't need to be a fixed point, I need the formatting to reflect the time the document is opened. so NOW() would be a sufficient reference point surely.

 

[jasonb75]

NOW() will always be the current time, not the time the workbook was opened.

Type =NOW() into a cell and format it as hh:mm:ss

Type somehting into another cell, the time of NOW() will change. Double click between 2 colums so that the cells autofit to the data, NOW() will change. Apply a filter to your data, NOW() will chage. It will not stay fixed at the time the workbook was opened. So, if you want it fixed, then yes, you do need to go that deep.