Simple Date/Time Macro

  • Thread starter Thread starter Tom Hall
  • Start date Start date
T

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
 
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
 
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
 
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.
Click to expand...

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
 
I can only guess that since you do not include the seconds in the format
Excel ignores them.


Gord
 
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.
 
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?
Click to expand...
 
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?
Click to expand...
 
Back
Top