Finding a time span

wallaceka

New Member
Joined
Apr 3, 2002
Messages
15
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.
 

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college
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*
 
Upvote 0
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
 
Upvote 0
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?
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
*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
 
Upvote 0
Now I'm blushing as Dave smiles at my very easy, short solution...
It's the thought/effort that counts! Right?
 
Upvote 0

Forum statistics

Threads
1,213,489
Messages
6,113,952
Members
448,535
Latest member
alrossman

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top