VB code to place rounded time beside cell of present date

  • Thread starter Thread starter AA Arens
  • Start date Start date
A

AA Arens

Hi

I am looking for the VB code for a button that places the time into
the cell? The time should be placed into a cell right of the cell that
is marked a the present date

A B
21
22
23

23 July is present date, format "d". The button places the present
time.

Is it possible to have that time rounded off to the last 15 min
quarter?

07.18 > 07.15
07.27 > 07.15

So:

A B
21 07.15
22 07.45



Thanks a lot.

Bart
 
Bart,

Sub BartMacro()
Dim myR As Range
Set myR = Range("A:A").Find(What:=Format(Now, "d"))
myR.Offset(0, 1).Value = Int((Now - Int(Now)) * 96) / 96
myR.Offset(0, 1).NumberFormat = "hh:mm"
End Sub

HTH,
Bernie
MS Excel MVP
 
Brnie,

It works if you takes the row number in consideration. I can even
choose Format(Now+5, "d")) if I start with A5.

How to deal with the month, as I have a column with

Column A
Month names (format M), this is a merged cell A5-A36 for January, then
A37-A65 for February.

and

Column B
Day B5-B36 (values 1-31), then B37-B65 (values 1-29)

In the third column I would like to have the time.


Bart
 
Unfortunately, you need to look for the date based on the cell format. So change your cell
formatting to match what you want to find, then change it back. This, of course, assumes that you
have real dates, not just day numbers. If you have day numbers, I would suggest that instead of
typing 1, 2, 3,... you actually enter 1/1/7 into cell B5, format it for custom d then copy
that down column B, and then use this macro:

Sub BartMacro2()
Dim myR As Range
Dim myDF As String
myDF = Range("B5").NumberFormat
Range("B:B").NumberFormat = "mm/dd/yy"
Set myR = Range("B:B").Find(What:=Format(Now, "mm/dd/yy"))
myR.Offset(0, 1).Value = Int((Now - Int(Now)) * 96) / 96
myR.Offset(0, 1).NumberFormat = "hh:mm"
Range("B:B").NumberFormat = myDF
End Sub

HTH,
Bernie
MS Excel MVP
 
Back
Top