Simple Date/Time Macro

[Tom Hall]

ActiveCell.FormulaR1C1 = "9/16/2009"
Range("B1").Select
ActiveCell.FormulaR1C1 = "9:25 AM"
Range("C1").Select
End Sub

I want to set up a simple workbook that enters the above information on the
current line in the sheet. When I recorded the keystrokes I wanted, I ended
up with the following macro, which only works once.

Starting with the second line, running the macro puts the date in the right
place, skips Column B and then leaves the cursor in R1C1.

How do I get this macro to enter all the information on the current line
(date in column 1, time in column 2)?

TIA,

Tom

 

[Dave Peterson]

I'd put the date and time in the same cell. It should make date/time arithmetic
a little easier:

With activecell.entirerow.cells(1) 'column A of the row with the activecell
.numberformat = "mm/dd/yyyy hh:mm:ss"
.value = now
end with

 

[Don Guillett]

Range("B1")= "9/16/2009"
Range("C1")= "9:25 AM"

 

[Gord Dibben]

Sub NOWTIME()
ActiveCell.Value = Format(Now, "mm/dd/yyyy")
ActiveCell.Offset(0, 1).Value = Format(Now, "h:mm:ss AM/PM")
End Sub

You can also combine this into one cell if you wanted.


Gord Dibben MS Excel MVP

 

[Tom Hall]

Sub NOWTIME()
ActiveCell.Value = Format(Now, "mm/dd/yyyy")
ActiveCell.Offset(0, 1).Value = Format(Now, "h:mm:ss AM/PM")
End Sub

You can also combine this into one cell if you wanted.

Thanks, Gord (and the others who responded)!

This does exactly what I was looking for. I was able to figure out how to
get the cursor to end up in column 3 after inserting the date and time.

Sub NOWTIME()
ActiveCell.Value = Format(Now, "mm/dd/yyyy")
ActiveCell.Offset(0, 1).Value = Format(Now, "h:mm:ss AM/PM")
ActiveCell.Offset(0, 2).Select
End Sub

An interesting note: I changed the time format string to "h:mm AM/PM" but I
still get output that includes seconds, but the value is always ":00"


Tom

 

[Gord Dibben]

I can only guess that since you do not include the seconds in the format
Excel ignores them.


Gord

 

[Dave Peterson]

Depending on your windows regional setting, this could result in a wrong date.

ActiveCell.Value = Format(Now, "mm/dd/yyyy")

If the date is ambiguous (like 11/12/2009), then even though you've formatted
the string in mdy order, excel will see the entry as a date and use the regional
settings to parse the entry. And if the user has a date order of dmy, that user
would see a date of December 11, 2009.

I'd use:

Sub NOWTIME()
with ActiveCell
.numberformat = "mm/dd/yyyy"
.Value = date 'not Now--since you don't want the time in the cell
with .offset(0,1)
.numberformat = "h:mm AM/PM"
.value = Time
end with
end with
End Sub

And since the .value include the seconds, the cell will include the seconds--but
the numberformat will hide it.

 

[Don]

Why not do it easy: =TODAY() and enter it directly into the cell
you want it in?

 

[Dave Peterson]

That formula will recalculate each time excel recalcs. You'd have to change it
a value after you enter the formula.

The original poster wanted the date in one cell and the time in another.

Why not do it easy: =TODAY() and enter it directly into the cell
you want it in?

 

[Gord Dibben]

Perhaps Tom wants a static date and time?

Perhaps the macro method is part of a grand plan?

But there are easier methods of entering a static date or time.

CTRL + ; or SHIFT + CTRL + ;


Gord

Why not do it easy: =TODAY() and enter it directly into the cell
you want it in?

 

[Tom Hall]

Perhaps Tom wants a static date and time?

I do indeed.


Tom