Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 14

Thread: Finding a time span

  1. #1
    New Member
    Join Date
    Apr 2002
    Posts
    14
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.

  2. #2
    Board Regular
    Join Date
    Mar 2002
    Location
    Cincinnati, Ohio, USA
    Posts
    6,824
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

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

  3. #3
    New Member
    Join Date
    Apr 2002
    Posts
    14
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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*

  4. #4
    Board Regular
    Join Date
    Mar 2002
    Location
    Cincinnati, Ohio, USA
    Posts
    6,824
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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 ]

  5. #5
    New Member
    Join Date
    Apr 2002
    Posts
    14
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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?

  6. #6
    MrExcel MVP
    Join Date
    Mar 2002
    Location
    Michigan USA
    Posts
    11,454
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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 ]

  7. #7
    New Member
    Join Date
    Apr 2002
    Posts
    14
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  8. #8
    New Member
    Join Date
    Apr 2002
    Posts
    14
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    *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

  9. #9
    Board Regular
    Join Date
    Feb 2002
    Location
    Calgary, Alberta Canada
    Posts
    3,428
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default



    =B2-A2

    Format custom d h:mm


  10. #10
    Board Regular
    Join Date
    Mar 2002
    Location
    Cincinnati, Ohio, USA
    Posts
    6,824
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

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

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •