Hi
I coulc not figure out a formula that took less than a page so I cheated with VBA code.
Paste this in your sheet module.
A double-click on column A or B inserts the current date and time.
When double-clicking on B, C is filled with a result such as "5 days. 1 hrs. 20 min."
If there is already a time entered, a double-click will not change it.
If one of these times is manually edited, just double-click on column B to update the results in C
Try it out
Tom
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Dim dStart As Date
Dim dEnd As Date
If Target.Column = 1 And Not IsDate(Target) Then _
Target = Date & " " & Time
If Target.Column = 2 Then
If Not IsDate(Target) Then Target = Date & " " & Time
If IsDate(Target.Offset(0, -1).Value) Then
dStart = Target.Offset(0, -1).Value
dEnd = Target.Value
Target.Offset(0, 1).Value = _
DateDiff("d", dStart, dEnd) & " days. " & _
DateDiff("h", dStart, dEnd) - (DateDiff("d", dStart, dEnd) * 24) & " hrs. " & _
DateDiff("n", dStart, dEnd) - (DateDiff("h", dStart, dEnd) * 60) & " min. "
End If
End If
End Sub
This message was edited by TsTom on 2002-04-04 07:45