Time difference

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am trying to add a timer to a spreadsheet. The timer must show the difference between the time that data was entered in say cel A1 and the time when data was entered in say cell R1(might be between 5 to 60 minutes)
I have tried various uses of NOW() which recalculates everytime to the current time
I am currently entering the time manually to calculate the difference
I dont need an actual time....just the difference ..IE: a timer between data entry times
Is there a way to make the NOW() command static in a cell so that the time value created with NOW() does not change 10 minutes later when I enter NOW() in another cell

Thank
 
Hi Alien,

You need VBA for this

This code puts it into B1. To enter it, right-click on the sheet tab, select
View Code and paste the code in

Dim firstTime As Date

Private Sub Worksheet_Change(ByVal Target As Range)

On Error GoTo ws_exit
Application.EnableEvents = False
If Not Intersect(Target, Range("A1")) Is Nothing Then
firstTime = Now
ElseIf Not Intersect(Target, Range("R1")) Is Nothing Then
With Range("B1")
.Value = Now - firstTime
.NumberFormat = "hh:mm:ss"
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

Alien1155 said:
I am trying to add a timer to a spreadsheet. The timer must show the
Click to expand...
difference between the time that data was entered in say cel A1 and the time
when data was entered in say cell R1(might be between 5 to 60 minutes).
I have tried various uses of NOW() which recalculates everytime to the current time.
I am currently entering the time manually to calculate the difference.
I dont need an actual time....just the difference ..IE: a timer between data entry times.
Is there a way to make the NOW() command static in a cell so that the time
Click to expand...
value created with NOW() does not change 10 minutes later when I enter NOW()
in another cell?
 
Back
Top