Inserting static date/time

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.

0
anonymous (74722)
7/29/2004 7:56:48 PM
excel.misc 78881 articles. 5 followers. Follow

5 Replies
157 Views

Similar Articles

[PageSpeed] 59

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

-- 
Bernard Liengme
www.stfx.ca/people/bliengme
remove CAPS in e-mail address


"Mark" <anonymous@discussions.microsoft.com> wrote in message
news:6bc901c475a6$2eb066b0$a401280a@phx.gbl...
> 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.
>


0
bliengme5824 (3040)
7/29/2004 8:31:29 PM
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.

Mark wrote:
> 
> 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.

-- 

Dave Peterson
ec35720@msn.com
0
ec35720 (10082)
7/30/2004 2:03:09 AM
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!

>-----Original Message-----
>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
>
>-- 
>Bernard Liengme
>www.stfx.ca/people/bliengme
>remove CAPS in e-mail address
>
>
>"Mark" <anonymous@discussions.microsoft.com> wrote in 
message
>news:6bc901c475a6$2eb066b0$a401280a@phx.gbl...
>> 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.
>>
>
>
>.
>
0
anonymous (74722)
7/30/2004 3:32:43 PM
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


On Fri, 30 Jul 2004 08:32:43 -0700, "Mark"
<anonymous@discussions.microsoft.com> wrote:

>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!
>
>>-----Original Message-----
>>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
>>
>>-- 
>>Bernard Liengme
>>www.stfx.ca/people/bliengme
>>remove CAPS in e-mail address
>>
>>
>>"Mark" <anonymous@discussions.microsoft.com> wrote in 
>message
>>news:6bc901c475a6$2eb066b0$a401280a@phx.gbl...
>>> 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.
>>>
>>
>>
>>.
>>

0
Gord
7/30/2004 4:18:27 PM
Ignore me.  I'm an idiot!  I figured out what was wrong.  
Your code worked perfectly for my needs.  Thank you!


>-----Original Message-----
>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!
>
>>-----Original Message-----
>>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
>>
>>-- 
>>Bernard Liengme
>>www.stfx.ca/people/bliengme
>>remove CAPS in e-mail address
>>
>>
>>"Mark" <anonymous@discussions.microsoft.com> wrote in 
>message
>>news:6bc901c475a6$2eb066b0$a401280a@phx.gbl...
>>> 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.
>>>
>>
>>
>>.
>>
>.
>
0
anonymous (74722)
7/30/2004 4:26:08 PM
Reply:

Similar Artilces:

including date and time in one function
I am trying to make a worksheet that will return a value during certain time period. For example: On Monday at 8:00-9:00, I would lik it to display a different value than the same time on Tuesday. Also, need it to show a different value later on in the same day. I canno figure out if there is a way to combine DATEVALUE and TIMEVALUE, nor a i sure if a combination of the two is what is exactly what I want, bu it is somewhere along those lines. I would appreciate any help tha anyone can give me -- Message posted from http://www.ExcelForum.com Hi you can add them. e.g. try =IF(AND(A1>=DAT...

Single email delivered 10 times
Does anyone know of a reason why an email sent a single time would be delivered to the recipient 10 times? This is only happening to one account. No one else has complained about a similar issue. This user sent a single email to a customer and they received it 10 times. I asked the user to email me at an external address and I received it twice. Does the user have any Outlook Rules setup? Have you tried recreating the users Outlook Profile? -- John Oliver, Jr. MCSE, MCT, CCNA, Exchange MVP Microsoft Certified Partner "Kim" <anonymous@discussions.microsoft.com> ...

today's date
which formula should i use to insert today's date which update daily automatically. =TODAY() "aditya" wrote: > which formula should i use to insert today's date which update daily > automatically. "aditya" wrote: > which formula should i use to insert today's date which update daily > automatically. =NOW() In the cell were you want the date, but don't forget to format it, so it shows up as a date, not a number. Hi, =Today() "aditya" wrote: > which formula should i use to insert today's date which update daily &g...

Insert As Link Missing
I have tried to show someone how to insert a file as a link, but can't get that option to show on their version of Outlook (2003 with all SP's). I have tried changing mail format to use Word as editor or just HTML and neither seems to work. What am I missing to have this option available? Thanks! In news:%23tc1RE6rGHA.1924@TK2MSFTNGP05.phx.gbl, Karl Burrows <karlspam@spam.yourbeacon.com> typed: > I have tried to show someone how to insert a file as a link, but > can't get that option to show on their version of Outlook (2003 with > all SP's). I have ...

Transaction Date VS Business Date
Our accounting department needs/wants to have all RMS SO reporting by business date opposed to transaction date. Usually these are the same unless you have sales and batches that close after midnight. For our store I would like to set 3 a.m. as the business day closing time. So when I run any type of reports I would like to get the information for a business date (range) starting at 3 a.m. till 3 a.m. the next day. I guess I could accomplish this by subtracting 3 hours from all the transaction (date and) times but I would have to do this for every single report which would be a lot of ...

Posting date on worksheet
I have a number of worksheets in a workbook where I save different scenarios. I would like to place on the worksheet the last date that it was modified. I can tell when I last worked on the workbook from the file description but I'd like to know the last time I modified a worksheet. Any ideas? Thanks. Michael Have a look at http://www.mcgimpsey.com/excel/timestamp.html Charlie O'Neill >-----Original Message----- >I have a number of worksheets in a workbook where I save >different scenarios. I would like to place on the >worksheet the last date that it was modified...

Insert Multiple Rows #4
I am working with a spreadsheet that has about 1000 rows of data in it. I need to insert 14 rows between each current record. I know this can be done using a numbering system and then sorting the page, but I am having trouble getting excel to recognize the counting pattern. Any help would be great. -- ucf1020 ------------------------------------------------------------------------ ucf1020's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=17055 View this thread: http://www.excelforum.com/showthread.php?threadid=480678 Edited to meet your needs; originally f...

Printer on all the time
A friend of mine called about a trouble with his computer(XPsp3).He claims that everytime he gets on the internet using IE8,his printer comes on and starts printing all the web pages. I googled,but I cannot find any references to this problem.He unplugged the printer from the computer,he still gets the same thing.He claims the trouble started when he got an email message from a Hotel. Thanks Just in case the problem is still active: If the problem started after receiving a message, the message may have carried a virus... so the computer should be scanned for virus or trojan...

Date conversion, PLEASE HELP
I am importing data from an OBDC datasource. One of the fields is a date field that imports in this format "20040818", as a number. I am trying to convert this value to a date format and have had no luck. If anyone can provide assistance, I am grateful... MyDate = DateSerial(Left([ImportedDate], 4), Mid([ImportedDate], 5, 2), Right([ImportedDate], 2)) -- Ken Snell <MS ACCESS MVP> "shawn" <shawn.abbott@mrgasket.com> wrote in message news:8ac901c4854e$3aa483e0$a601280a@phx.gbl... > I am importing data from an OBDC datasource. One of the &g...

Counting dates within groups
Hi Is it at all possible to calculate or count groups of dates in a lis when they have a gap of 2 days between them? It's kind of hard to explain The attachment has the data I'm trying to work from including description of the result. Cheers Joe Attachment filename: timesheet test.xls Download attachment: http://www.excelforum.com/attachment.php?postid=56391 -- Message posted from http://www.ExcelForum.com ...

Add System Date to Chart
Hi All, Is there any way of display the system date, either within the title of the chart, or anywhere else? Cheers. Bill. Hi, You could link the chart title to a cell which contains the system data. Once you have added the chart title to link it to a cell enter the full cell address in the formula bar. =Sheet1!$A$1 http://www.andypope.info/tips/tip001.htm Cheers Andy -- Andy Pope, Microsoft MVP - Excel http://www.andypope.info "Bill" <bill@bill.com> wrote in message news:%23onkuRxFIHA.4584@TK2MSFTNGP03.phx.gbl... > Hi All, > > Is there any way of displa...

workflow manager not opening
I get the following error when trying to open any of the workflow tools ----------------------------------------------------------------------------------------------- An error occurred while loading the organisation's date, time, and number format settings. Those settings are incompatible with the formats already used in Workflow Manager. To sync them, you can make changes to System Settings in the System Customisation area of Microsoft CRM Server. ----------------------------------------------------------------------------------------------- No-one has changed this settings, but our...

date difference in mm/yyyy format
I have two date colums StartDate ReadDate I want to find all the readdates that are in months prior to the startdate Eg if(readdate<startdate,"yes","no") the problem is that if the readdate is in the same month (or any later date) as the startdate I want a "no' returned. Is there any way to compare to dates in a mm/yyyy format? Hi Ian =if(Month(readdate)<month(startdate),"yes","no") -- Regards Roger Govier Ian wrote: > I have two date colums > StartDate > ReadDate > > I want to find all t...

Wrong Due Date on APAYs
My APAYs show the wrong due date on the Bills Summary page. For example, a monthly mortgage payment shows the next due date being Mar 2007. A monthly car payment, with only five payments left, shows the next due date being in 2011! The next payment on the Bill Calendar is also missing -- sometimes one or more -- not necessarily related to the missing payment dates from looking at the Bill Summary. This was a problem in Money 2003 before I upgr^H^H^Hchanged to Money 2007. After I installed M2007, I did the File Repair and also the Remove All Bills Data options. This seemed to correct the p...

How to insert alternate signature Outlook 2003?
How to insert alternate signature Outlook 2003? Outlook 2003 inserts the default signature assigned to a particular email account automatically. I have another signature created which includes my address & phone. It is not a default signature for any account (don't want it to be) but I would like to insert that signature on occasion. How do I do it? JJ John "J.J." Jackson WordMail does not include an Insert | Signature command like the regular = Outlook editor. If you want to be able to insert a signature into a = WordMail message manually, you can create an AutoText ent...

Incorrect time for incoming email
This past Sunday, I went from Daylight savings time to standard time. The problem I have is that email and meeting requests are showing the wrong time. For example if I send a meeting request for 2:00 it is showing up on the recipients calander as 1:00. The time on the DC (2000), Exchange servers (2003) and clients are correct. There must be a setting I'm missing. Any ideas? I have recently learned there is a calendar setting in Outlook in Tools/Options/calendar options/time zone. If the check in Daylight savings is not in there then times will not correspond with Windows tim...

run-time error 381 on journals
When I bring up journals either the pos or manager I get a "Run-time error '381' Invalid property array index". When I go to receipt format and go to properties. I get a "runtime error 6 overflow". The other registers work fine, so it not a database problem. I tried uninstalling and reinstalling and still no luck. I am on RMS 2.0.0126 version. Thanks Dave May be printer driver issue or windows printer driver setting of default printer For receipt printer are you using windows or opos drivers? Check the windows printer properties in the registers that work okay...

Dates and Cell Values
How can I get the minimum value from column C for all 8/24/2009 dates in Column A? I tried some index and match functions but still doesn't work. Column A Column B Column C Column D 8/24/2009 12:12:56 AM 113 904 8/24/2009 12:52:56 AM 114 908 8/24/2009 1:32:56 AM 114 907 8/24/2009 2:12:56 AM 112 897 8/24/2009 2:52:56 AM 113 902 8/25/2009 12:13:57 AM 82 654 8/25/2009 12:53:57 AM 81 650 8/25/2009 1:33:57 AM 81 650 8/25/2009 2:13:57 AM 81 650 8/25/2009 2:53:57 AM 81 649 8/25/2009 3:33:57 AM 81 647 8/25/2009 4:13:57 AM 81 646 8/25/2009 4:53:57 AM 81 643 8/...

Current Time
I have a datasheet subform that has a time field (short time) that has a default value of time(). WHen I open the form, the new record row in the datasheet shows the current time. If I do not enter anything in this form for a few minutes, the new record will have the current time as when the form was open. I have an evidence database that I would like the current time to enter when an entry is made, but if I am busy tagging information and don't make the entry for 3 minutes, the time is off. Any suggestions on getting the current time when the entry is made - now when the form is opened?...

Date Subtraction #2
Hi I run Win2K with Excel 2K. I would like to enter a date in a cell (eg Oct-05) and have the preceding 11 cells automatically put the previous months in. Example: In cell A15 = Oct-05 (entered) In cell A14 = Sep-05 (automatically) In cell A13 = Aug-05 (automatically) In cell A12 = Jul-05 (automatically) etc etc etc Is there a formula that can do this? Any help will be much appreciated...thanks ! John On Wed, 9 Nov 2005 16:34:57 -0800, "John Calder" <JohnCalder@discussions.microsoft.com> wrote: >Hi > >I run Win2K with Excel 2K. > >I would like to ent...

% query based on dates.
Hi, I need to run a query to find out % of site visits made within 10 days of receipt of a case. Both fields are of the date type and the site visit date needs to contain no NULL values. The fields are: - DateRecd = date of receipt of case DateR = date of site visit Anyone able to help? Thanks, Andy. abyron@solihull.gov.uk -- Message posted via http://www.accessmonster.com Sorry, the DateR field is a text field and not a date field for some reason. Andy. abyron@solihull.gov.uk -- Murdoc Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-queri...

save document which a date and deleted the old document
I like to save an excel document with the name which is in A1 and the date of today which is in B1 The old document must be deleted Who can help Thanks Peter Here's a way: Sub SaveAndDate() Dim lStr_CurFileName As String With ThisWorkbook lStr_CurFileName = .FullName .SaveAs .Path & "\" & Range("Sheet1!A1") & " " & Format(Range("Sheet1!B1"), "yyyymmdd") & ".Xls" End With Kill lStr_CurFileName End Sub -- Regards Andy Wiggins www.BygSoftware.com Home of "Save and BackUp...

times part of cell content
I got a column like this: 11/40 35/10 101/100 250/40 I need to times the number before the / with 2 in each row so I get 22/40 70/10 202/100 500/40 Anyone know how I can do this? Thanks Mike "Mike Mike" <mm@mitechhostREMOVE.com> wrote in message news:1rxVf.96540$Fw6.43301@tornado.tampabay.rr.com... >I got a column like this: > 11/40 > 35/10 > 101/100 > 250/40 > > I need to times the number before the / with 2 in each row so I get > 22/40 > 70/10 > 202/100 > 500/40 > > Anyone know how I can do this? > > Thanks > Mike Mi...

Insert Table with Tab Key
How can I disable this function to be able to insert normal Tab stops with the Tab key? -------- Original-Nachricht -------- > How can I disable this function to be able to insert normal Tab stops with > the Tab key? 1. not possible 2. workaround: http://blogs.msdn.com/olya_veselova/archive/2008/04/07/workaround-for-turning-off-the-tab-key-creating-a-table-instead-of-a-regular-tab-in-onenote.aspx Bernd ...

SQL Insert / Integration Manager
Ok, I have been asked to upload into the database all ISO3166 2 character country code listings. The table I have identified is VAT1001. Fields are CCode, CCodeDesc, ECFLAG, NOTEINDX, DEX ROW ID. Now, I have no problem putting in data for the first 3 fields,b ut NoteIndx and Dex Row ID are system generated. I have Integration Manager for Distribution but I do not see where it would have me upload to VAT1001. So, 1. is this possible with Integration Manager for Distribution 2. Is it possible to just do a SQL insert (via MS Access) and will GP during the next check links fix t...