MrExcel Publishing
Your One Stop for Excel Tips & Solutions

HELP! Auto calculating start times and time values

Posted by Wendy D on January 08, 2001 11:22 AM

I need help to auto insert the date and time, and then from a manually entered end time, calculate the total time between the two. Problem I encountered, if I use NOW(), it changes each time I do a calculation on the sheet. I am creating a project list that will calculate the time spent on projects without having to manually enter the start date and time with each project. Can anyone help?

Posted by Loren on January 08, 2001 11:39 AM

Control-semicolon enters the current date. Don't
know about time.

Posted by Loren on January 08, 2001 11:46 AM

How to enter the time

To enter the current time, press CTRL+SHIFT+: (colon).

Posted by cpod on January 08, 2001 12:23 PM

How about a VB procedure that enters the current date and time only in the row where you have entered an end time?

If Target.Column = 2 Then
ActiveSheet.Cells(Target.Row, 1) = Now()
End If

If you enter this code into the change event for the sheet it will enter the date only in the row you are updating. Change the the "2" in:
If Target.Column = 2 Then
to reflect the column you are entering the end time and change the "1" in

ActiveSheet.Cells(Target.Row, 1) = Now()

to indicate the column where you want the current date/time to be entered.