Finding a time span

[wallaceka]

I have a user that would like to track the time that elapses between the time he attempts to initiate contact with someone and the time they get back to him. Ideally, if this could be tracked down to the minute, that would be grand. The span could potentially be days or hours (worst case weeks). I know this is POSSIBLE; I am just not sure how to implement it. I have posted several times to this board, always with amazing results. Thanks for any help given, Kathi.

[Tom Schreiner]

Hi
Just for clarity...
Two times in two columns? Right?
Tom

[wallaceka]

I am not sure the user cares about presentation, just a start time column, an end time column, and I would suppose a third with a formula to calculate the time elapsed. Does that make sense? *ponder*

[Tom Schreiner]

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 ]

[wallaceka]

man, I feel like such a noodle. I have never used the vba aspect of excel successfully. I tried fudging around with it once or twice and I have all these old modules I cannot figure out how to get rid of. Can you suggest a really good book on vba with excel formulas type of thing?

[Yogi Anand]

On 2002-04-04 05:21, wallaceka wrote:
I have a user that would like to track the time that elapses between the time he attempts to initiate contact with someone and the time they get back to him. Ideally, if this could be tracked down to the minute, that would be grand. The span could potentially be days or hours (worst case weeks). I know this is POSSIBLE; I am just not sure how to implement it. I have posted several times to this board, always with amazing results. Thanks for any help given, Kathi.

Hi ponder:
Tom has given a VBA solution -- here is a formula -- I have not tested it for correctness, but try it:

in cell A2 3/28/02 10:05
in cell B2 4/4/02 11:07

in cell C2
=INT(B2-A2)& "days, "&INT(MOD(B2-a2,1)*24)&" hours, "&INT(MOD((B2-A2)*24,1)*60)&" minutes"

with the result as

7days, 1 hours, 1 minutes


Please post back if it works for you ... otherwise explain a little further and let us take it from there!


_________________
Yogi Anand

Edit: Deleted inactive website from hardcoded signature

[ This Message was edited by: Yogi Anand on 2003-01-19 14:10 ]

[wallaceka]

Yogi-
Thanks for the reply. The answer I received after testing was 0days, 0 hours, 0 minutes. I copied and pasted the formula to make sure I got it all.
Kathi

[wallaceka]

*blushing.bright.red*
I am sooooo dumb.
It works and wonderfully!! You guys are the greatest.
I hesitate to tell you that I pasted it into the wrong cell for testing purposes.

A million thanks!
Kathi

[Dave Patton]

=B2-A2

Format custom d h:mm

[Tom Schreiner]

Now I'm blushing as Dave smiles at my very easy, short solution...
It's the thought/effort that counts! Right?