Inserting static date/time

  • Thread starter Thread starter Mark
  • Start date Start date
M

Mark

I'm trying to create a spreadsheet that will allow me to
keep track of "Start Time", "Finish Time" and "Duration"
for phone calls.

I need to record the current date and time down to the
second - hh:mm:ss as the Duration of many items will be
less than 1 minute.

The CTRL-SHIFT-; shortcut to insert static date/time
doesn't appear to go down to seconds.

Any easy way I can get this?

I know I could use the =NOW() function but then I'd have
to immediately paste special>values to get the correct
time. I can't seem to create a macro that will paste
special>values either.

Any suggestions would be appreciated.
 
Try this subroutine.
Format the cell to be used to display time in hours min sec
Put a button on worksheet to run the sub
Any questions?

Sub Macro1()
'
' Macro1 Macro
' Macro recorded 29/07/2004 by Bernard Liengme
'

ActiveCell.FormulaR1C1 = "=NOW()"
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
End Sub

Best wishes
 
I think I stole this from J.E. McGimpsey:

Put this in a workbook in your XLStart folder (maybe personal.xls)

All of these go in a general module:

Option Explicit
sub auto_open()
Application.OnKey "+^:", ThisWorkbook.Name & "!CtrlShiftColon"
end sub

sub auto_close()
Application.OnKey "+^:"
end sub

Sub CtrlShiftColon()
On Error Resume Next
With Selection
.NumberFormat = "hh:mm:ss"
.Value = Time
End With
If Err.Number <> 0 Then
MsgBox "Time not inserted"
Err.Clear
End If
On Error Goto 0
End Sub

It steals the ctrl-colon (ctrl-:) and puts in the seconds.
 
Thanks for the code. I did try putting this in but when
I run it, I get a "Compile Error: Syntax Error" on the
line that contains:

Selection.PasteSpecial Paste:=xlPasteValues,

I copied the macro exactly as you had it. Any ideas?
Thanks again!
 
Mark

You have been hit by line-wrap.

Try this.......

Sub Macro1()
ActiveCell.FormulaR1C1 = "=NOW()"
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, _
Operation:=xlNone,SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
End Sub

Gord Dibben Excel MVP
 
Ignore me. I'm an idiot! I figured out what was wrong.
Your code worked perfectly for my needs. Thank you!
 
Back
Top