VB code to place rounded time beside cell of present date

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

AA Arens - 27 Jun 2007 08:49 GMT

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

Reply to this Message

Bernie Deitrick - 27 Jun 2007 14:12 GMT

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

> Hi
>
[quoted text clipped - 25 lines]
>
> Bart

Reply to this Message

AA Arens - 27 Jun 2007 15:05 GMT

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

On Jun 27, 8:12 pm, "Bernie Deitrick" <deitbe @ consumer dot org>
wrote:

> Bart,
>
[quoted text clipped - 42 lines]
>
> > Bart

Reply to this Message

Bernie Deitrick - 27 Jun 2007 15:20 GMT

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

> Brnie,
>
[quoted text clipped - 64 lines]
>>
>> > Bart

Reply to this Message

Rate this thread: