Date/Time stamp with one stroke?

[Guest]

I know that you can have Excel insert the current date with CTRL + ; and the
current time with CTRL + SHIFT + :. Is there some way to insert both with
one command?

I know it seems like a small thing, but we have a worksheet for employees to
keep track of completed tasks. When they complete a given task, they need to
put the date and time of completion in a specific cell. However, over the
course of a day, they may be completing upwards of 250 tasks. Even lessening
the time to complete the time stamp by a single set of keystrokes would be
helpful.

 

[Guest]

As an additional note, I did try changing the formatting of the cells to Date
and Time, then using CTRL + ; to put in the current stamp. This does add a
time, but it will only add midnight.

 

[Jason Morin]

You could do it with a macro. Press ALT+F11, go to Insert

Module, and paste in the code below. Press ALT+Q to

close the Visual Basic Editor:

Sub InsertDate_Time()
With ActiveCell
.Value = Now
.NumberFormat = "mm/dd/yy h:mm AM/PM"
.Offset(1, 0).Select
End With
End Sub

---
Now go to Tools > Macro > Macros and make sure the macro
name is highlighted. Press "Options" and insert a letter
such as lowercase "a". Your shortcut is now Ctrl+a.

HTH
Jason
Atlanta, GA

 

[Guest]

Hi
Have you tried
=NOW() and then format as time / Custom h:mm:ss

HTH
Michael

 

[Guest]

Excellent! That works great. Thank you very much!

 

[Guest]

Just a thought, but if you enter a formula that includs the current time and
date, isn't is going to update itself constantly, every time you open or
recalculate your workbook? Thus, if you finish a job today and open the
workbook tomorrow, it'll show tomorrow's date!

One way to get around this is to convert the formula to a value using paste
special, once you've entered it into the cell. You would do it like this:

Sub InsertDate_Time()
With ActiveCell
.Value = Now
.NumberFormat = "mm/dd/yy h:mm AM/PM"
.Copy
.PasteSpecial Paste:=xlValues, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
End With
Application.CutCopyMode = False 'empties the clipboard
End Sub

Hope this helps

Pete

 

[Dave Peterson]

but you used .value (not .formula).

So the copy|paste special|values is not necessary.

 

[Guest]

Dave,

Whoops, you're quite right - that'll teach me to pass comment about other
peoples' perfectly satisfactory solutions!

Pete

 

[Dave Peterson]

Doesn't stop me!

Sometimes, the words change between viewings!

 

[Guest]

Great responses if you use word or excel. Any idea how to do this in
Outlook? -Erin ([email protected])

 

[Dave Peterson]

Maybe posting in an Outlook newsgroup would get better responses.