Find nearest time to NOW()

[JeffGrant]

Hi All,

To find the nearest time in a range to the current time i am using =Now() in a cell, then using copy/paste special value to "fix" the current time, so that I can find the nearest next time to Now.

I don't believe I can the NOW function in a SMALL or Index/Match formula because NOW is always increasing.

Can anybody suggest a better way to do this because I like to avoid using the Clipboard if I can?

Thanks

 

[awoohaw]

Here is a VBA Solution:

Sub PasteNow()
'
' PasteNow Macro
'

'
    ActiveCell.FormulaR1C1 = "=NOW()"
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
End Sub

 

[RobP]

Hi

not sure if this would work, but what about using LET(Tim,now(),try your formula here)

I‘m wondering if by setting Tim to be Now() if you can then use it in the formula after without it changing ?

just a thought
Rob

 

[awoohaw]

Here is a user defined function.

Create a module in VBA (Alt-F11, Insert Module) Paste this code;

Function PNow()
Dim x As Variant
x = Now
PNow = x
End Function

Then Click: Debug, Compile VBAProject
then go back to your worksheet, and type =PNow in any cell.
Hit enter,
You'll need to format the cell for Date and Time.

I believe it will only recalculate when you recalculate the Cell itself or a cell using the UDF. It will not recalculate when pressing F9. I am not sure if it will recalculate when re-opening the workbook.