+ Reply to Thread
Results 1 to 7 of 7

Can this even be done? Tracking Date / Time Elapsed

  1. #1
    scd
    Guest

    Can this even be done? Tracking Date / Time Elapsed

    I'm trying to create a worksheet which records elapsed time between Date /
    Time Received and Date / Time Delivered. So far, so good. Problem is, the
    elapsed time I get really needs to use some form of Networkdays, excluding
    Holidays, and (here's the really tricky part) non-work hours. I've tried
    fiddling with various Ifs, but am not experienced enough at Excel to grasp
    why my formulas don't work.

    What I currently have is giving me total time elapsed, which is good:

    Received (A1) - 12/30/05 16:08
    Delivered (B1) - 1/3/06 9:20
    Elapsed (C1) - =(A1-B1) 3:17:12 (formatted Custom - d:h:mm)

    The result I actually want in this example is 0:2:12, based on 52 elapsed
    minutes on 12/30/05 and 1:20 on 1/3/06 (clock stops between 5PM & 8AM),
    excluding weekends, holiday (1/2/03) & overnight hours (17:00-08:00). Is
    there any *possible* way to get there from here?

    Any help greatly appreciated, but as I said, I'm not all that Excel savvy,
    so type... real... slow...

  2. #2
    scd
    Guest

    RE: Can this even be done? Tracking Date / Time Elapsed

    First post, first goof already. My C1 formula actually is =(B1-A1)



  3. #3
    Roger Govier
    Guest

    Re: Can this even be done? Tracking Date / Time Elapsed

    Hi

    Set up a list of holiday dates somewhere on your sheet and use
    Insert>Name>Define Holidays > Refers to your range of cells.

    Then
    =(TIME(17,0,0)-MOD(A1,1))+(MOD(B1,1)-TIME(8,0,0))+(NETWORKDAYS(A1,B1,holidays)-2)*9

    Starting from the end of the formula, Networkdays returns the total
    elapsed working days between the start and end dates, excluding any days
    within the range of dates defined by holidays. Since we are dealing with
    the start date and end date in the earlier part of the formula, then you
    need to deduct 2 from the result, and multiply the result by 9 to cover
    the hours for each full day elapsed.

    The first part of the formula, uses MOD() to extract the time from the
    day date value in the cell, and deducts this from 5 pm to calculate
    elapsed hours on start day. The middle part does the same, but takes 8
    am away from the time value to calculate elapsed hours on the final day.

    --
    Regards

    Roger Govier


    "scd" <scd@discussions.microsoft.com> wrote in message
    news:D2B8F598-F7BD-4D48-B06F-E432DBA2CDD0@microsoft.com...
    > I'm trying to create a worksheet which records elapsed time between
    > Date /
    > Time Received and Date / Time Delivered. So far, so good. Problem
    > is, the
    > elapsed time I get really needs to use some form of Networkdays,
    > excluding
    > Holidays, and (here's the really tricky part) non-work hours. I've
    > tried
    > fiddling with various Ifs, but am not experienced enough at Excel to
    > grasp
    > why my formulas don't work.
    >
    > What I currently have is giving me total time elapsed, which is good:
    >
    > Received (A1) - 12/30/05 16:08
    > Delivered (B1) - 1/3/06 9:20
    > Elapsed (C1) - =(A1-B1) 3:17:12 (formatted Custom - d:h:mm)
    >
    > The result I actually want in this example is 0:2:12, based on 52
    > elapsed
    > minutes on 12/30/05 and 1:20 on 1/3/06 (clock stops between 5PM &
    > 8AM),
    > excluding weekends, holiday (1/2/03) & overnight hours (17:00-08:00).
    > Is
    > there any *possible* way to get there from here?
    >
    > Any help greatly appreciated, but as I said, I'm not all that Excel
    > savvy,
    > so type... real... slow...




  4. #4
    scd
    Guest

    Re: Can this even be done? Tracking Date / Time Elapsed

    Oh... my... God, what a beautiful thing!
    I followed your instructions, c/p'ed the formula (i especially liked that
    part!) and it worked perfectly.

    Thank you SO much, Roger!


    "Roger Govier" wrote:

    > Hi
    >
    > Set up a list of holiday dates somewhere on your sheet and use
    > Insert>Name>Define Holidays > Refers to your range of cells.
    >
    > Then
    > =(TIME(17,0,0)-MOD(A1,1))+(MOD(B1,1)-TIME(8,0,0))+(NETWORKDAYS(A1,B1,holidays)-2)*9
    >
    > Starting from the end of the formula, Networkdays returns the total
    > elapsed working days between the start and end dates, excluding any days
    > within the range of dates defined by holidays. Since we are dealing with
    > the start date and end date in the earlier part of the formula, then you
    > need to deduct 2 from the result, and multiply the result by 9 to cover
    > the hours for each full day elapsed.
    >
    > The first part of the formula, uses MOD() to extract the time from the
    > day date value in the cell, and deducts this from 5 pm to calculate
    > elapsed hours on start day. The middle part does the same, but takes 8
    > am away from the time value to calculate elapsed hours on the final day.
    >
    > --
    > Regards
    >
    > Roger Govier




  5. #5
    Forum Moderator daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,674
    Hi Roger & scd

    Roger's suggested formula will only work correctly when the delivery date is the next working day after the received date as in the example quoted - the 9at the end needs to be 9 hours, i.e.

    =(TIME(17,0,0)-MOD(A1,1))+(MOD(B1,1)-TIME(8,0,0))+(NETWORKDAYS(A1,B1,holidays)-2)*TIME(9,0,0)

    however you can simplify this to

    =MOD(B1,1)-MOD(A1,1)+(NETWORKDAYS(A1,B1,holidays)-1)*"09:00"

    or to return a blank if either A1 or B1 are blank

    =IF(COUNTBLANK(A1:B1)=0,MOD(B1,1)-MOD(A1,1)+(NETWORKDAYS(A1,B1,holidays)-1)*"09:00","")

    Finally, is it possible that the delivery date could be outside work hours, e.g. on a Saturday, what result would be required then?

  6. #6
    Roger Govier
    Guest

    Re: Can this even be done? Tracking Date / Time Elapsed

    Hi daddylonglegs

    Nice catch of my error in not using * Time(9,0,0) at the end (or even
    more simply * "09:00")!
    Your simplification to just using the subtraction of the MOD of the
    times, and using Netwokdays -1 rather than 2 is a masterly stroke.
    Well done.

    --
    Regards

    Roger Govier


    "daddylonglegs"
    <daddylonglegs.221dca_1137933301.7018@excelforum-nospam.com> wrote in
    message
    news:daddylonglegs.221dca_1137933301.7018@excelforum-nospam.com...
    >
    > Hi Roger & scd
    >
    > Roger's suggested formula will only work correctly when the delivery
    > date is the next working day after the received date as in the example
    > quoted - the 9at the end needs to be 9 hours, i.e.
    >
    > =(TIME(17,0,0)-MOD(A1,1))+(MOD(B1,1)-TIME(8,0,0))+(NETWORKDAYS(A1,B1,holidays)-2)*TIME(9,0,0)
    >
    > however you can simplify this to
    >
    > =MOD(B1,1)-MOD(A1,1)+(NETWORKDAYS(A1,B1,holidays)-1)*"09:00"
    >
    > or to return a blank if either A1 or B1 are blank
    >
    > =IF(COUNTBLANK(A1:B1)=0,MOD(B1,1)-MOD(A1,1)+(NETWORKDAYS(A1,B1,holidays)-1)*"09:00","")
    >
    > Finally, is it possible that the delivery date could be outside work
    > hours, e.g. on a Saturday, what result would be required then?
    >
    >
    > --
    > daddylonglegs
    > ------------------------------------------------------------------------
    > daddylonglegs's Profile:
    > http://www.excelforum.com/member.php...o&userid=30486
    > View this thread:
    > http://www.excelforum.com/showthread...hreadid=502538
    >




  7. #7
    scd
    Guest

    Re: Can this even be done? Tracking Date / Time Elapsed

    Thanks, both! I realized the same-day problem a few days ago and had
    intended to check back in for clarification. Looks like you beat me to it.

    Your "if" statement was an even bigger help, as much of our documentation
    predates the use of time stamps, and just relies on dates.

    And as for your question about what happens during off-times, we basically
    told Mgmt that anything we don't get between 8-5 M-F is just stamped as of
    8AM the next workday.

    Again, thanks to both of you!

    "daddylonglegs" wrote:

    >
    > Hi Roger & scd
    >
    > Roger's suggested formula will only work correctly when the delivery
    > date is the next working day after the received date as in the example
    > quoted - the 9at the end needs to be 9 hours, i.e.
    >
    > =(TIME(17,0,0)-MOD(A1,1))+(MOD(B1,1)-TIME(8,0,0))+(NETWORKDAYS(A1,B1,holidays)-2)*TIME(9,0,0)
    >
    > however you can simplify this to
    >
    > =MOD(B1,1)-MOD(A1,1)+(NETWORKDAYS(A1,B1,holidays)-1)*"09:00"
    >
    > or to return a blank if either A1 or B1 are blank
    >
    > =IF(COUNTBLANK(A1:B1)=0,MOD(B1,1)-MOD(A1,1)+(NETWORKDAYS(A1,B1,holidays)-1)*"09:00","")
    >
    > Finally, is it possible that the delivery date could be outside work
    > hours, e.g. on a Saturday, what result would be required then?
    >
    >
    > --
    > daddylonglegs
    > ------------------------------------------------------------------------
    > daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486
    > View this thread: http://www.excelforum.com/showthread...hreadid=502538
    >
    >


+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1