+ Reply to Thread
Results 1 to 14 of 14

Calculate Hours Worked, Minus Weekends

  1. #1
    Registered User
    Join Date
    11-11-2009
    Location
    Chester, England
    MS-Off Ver
    Excel 2016
    Posts
    38

    Calculate Hours Worked, Minus Weekends

    OK there is something else too, I've figured out how to do the above, but, there is another factor to consider.

    Basically I need to calculate how long a ticket is open in our system before being resolved. I don't want to count weekends, and if the ticket is 'suspended', I don't want to count that either. There is also the factor that the ticket 'un-suspend' date may be later than the ticket 'closed' date. Which is the bit that's throwing me.

    So, I have the following fields

    Ticket Open, Ticket Closed, Ticket Suspended Date, Ticket Unsuspended Date

    A sample ticket might be (using above fields)

    02/11/09 09/11/09 04/11/09 30/11/09

    That 'should' equal two days (16 hours) as the Unsuspend date falls after the close date so it was suspended from the 4th until closure.

    Now I want to know, in hours (8 hour day) how long that ticket took to resolve (i.e close), remembering you can't count the time it was suspended, or any time that fell over a weekend. Also not all tickets are suspended.

    I can do it as long as I don't try the weekend bit (using NETWORKDAYS), as soon as I do that it all goes nuts

    Any help greatly appreciated, it's doing my head in! And I'm sure it's something simple I'm missing.

  2. #2
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

    Re: Need to Calculate Hours Worked, Minus Weekends

    Welcome to the Board.

    I'm sure there's a more elegant method but would the below return for you the correct result ?

    =NETWORKDAYS(A2,IF(C2,C2,B2),holidays)-1+MAX(0,NETWORKDAYS(IF(D2,D2,B2),B2,holidays)-1)
    Multiply about output by 8 if you wish to see in decimal hours.

    Assumes A2 holds Open, B2 Close, C2 Suspended (optional), D2 Unsuspended (optional) and holidays is named range containing public holidays to be excluded (optional)

  3. #3
    Registered User
    Join Date
    11-11-2009
    Location
    Chester, England
    MS-Off Ver
    Excel 2016
    Posts
    38

    Re: Need to Calculate Hours Worked, Minus Weekends

    You, my friend, are an absolute star, that works perfectly from what I've tested. Thank you

    oops quick edit, I should have added this, the fields also have the actual time of the ticket i.e. 03/11/2009 11:32. During a weekday the clock would be running for 24 hours (3 x 8 hour shifts), using what you've given me already, can the output be in precise hours:mins format? If I change it just now to HH:MM it gives me 0:00 (showing 8.00 as decimal)
    Last edited by DonkeyOte; 11-11-2009 at 12:17 PM. Reason: unnecessary quote removed

  4. #4
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

    Re: Need to Calculate Hours Worked, Minus Weekends

    I'm afraid to say I'm not going to be around for a while - hopefully someone else can help you resolve in the meantime.

    In essence however the addition of a working hours requirement makes things a little more complex... in basic terms

    =NETWORKDAYS(A2,B2,holidays)-1+MOD(B2,1)-MOD(A2,1)

    would give you the result in time rather than days where A2 was start and B2 end however you will need to adapt the above along the lines of the earlier formula to account for suspended times etc...

    the above formula is c/o daddylonglegs

  5. #5
    Registered User
    Join Date
    11-11-2009
    Location
    Chester, England
    MS-Off Ver
    Excel 2016
    Posts
    38

    Re: Need to Calculate Hours Worked, Minus Weekends

    Thanks to both of you . I can't figure how to factor this new forumla in exactly, tried a few variations and it's not giving me the right numbers back. Can anyone help?

  6. #6
    Forum Expert darkyam's Avatar
    Join Date
    03-05-2008
    Location
    Houston, TX
    MS-Off Ver
    2013
    Posts
    2,191

    Re: Need to Calculate Hours Worked, Minus Weekends

    We might, but we'd need an example of what dates you're using, the result you'd expect, and the result you're getting with the formula. Could you please post an example workbook with dummy data?

  7. #7
    Registered User
    Join Date
    11-11-2009
    Location
    Chester, England
    MS-Off Ver
    Excel 2016
    Posts
    38

    Re: Need to Calculate Hours Worked, Minus Weekends

    Actually, looking at it again with better perspective, I *think* what DonkeyOte gave me yesterday does give me what I want, just doing some more tests and looks good.

    Thanks again for everyone's help, great forums

  8. #8
    Registered User
    Join Date
    11-11-2009
    Location
    Chester, England
    MS-Off Ver
    Excel 2016
    Posts
    38

    Re: Calculate Hours Worked, Minus Weekends

    OK it's not quite what I'm after but it's close

    What I want is the actual time, in hours/mins, that a ticket is 'active' i.e not suspended and 'open' during working hours (not weekends)

    So I have

    Open Time Close Time Suspended Time Unsuspended Time
    10/11/09 09:00 13/11/09 15:00 12/11/09 12:00 16/11/09 12:00

    Now for this, it should calculate the time in hours/mins between the open and close time, and ignore the time the ticket was suspended (it doesn't matter the unsuspend time is later than the close time but it mustn't affect calculations, as there are other tickets where the unsuspend time is before the close time)

    So I'd like the end result to say active time xx hours xx mins

  9. #9
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

    Re: Calculate Hours Worked, Minus Weekends

    if the clock is 24 hours as you implied you simply need to take dll's formula and modify it along the lines of the earlier days based calculation

    Please Login or Register  to view this content.
    Format above cell to use [hh]:mm format
    (certain assumptions are being made in regard to time entries (A:D) always falling on working days)

  10. #10
    Registered User
    Join Date
    11-11-2009
    Location
    Chester, England
    MS-Off Ver
    Excel 2016
    Posts
    38

    Re: Calculate Hours Worked, Minus Weekends

    I salute you sir, thank you once again! I think you've really cracked it this time.

  11. #11
    Registered User
    Join Date
    11-11-2009
    Location
    Chester, England
    MS-Off Ver
    Excel 2016
    Posts
    38

    Re: Calculate Hours Worked, Minus Weekends

    It's still not quite right, I was sure it was

    If I adjust the time now, the calculation is wrong i.e. see below

    Please Login or Register  to view this content.
    The last row for example should be 45:00 active, it was suspended for 3 hours over two days

    Using formula =SUM(NETWORKDAYS(A85,IF(C85,C85,B85))-1+MOD(IF(C85,C85,B85),1)-MOD(A85,1),MAX(0,NETWORKDAYS(IF(D85,D85,B85),B85)-1+MOD(IF(D85,D85,B85),1)-MOD(B85,1)))

    A=Open, B=Close, C=Suspend, D=Unsuspend

    *edit* wait I think I've fixed it, change formula to =SUM(NETWORKDAYS(A85,IF(C85,C85,B85))-1+MOD(IF(C85,C85,B85),1)-MOD(A85,1),MAX(0,NETWORKDAYS(IF(D85,D85,B85),B85)-1-MOD(IF(D85,D85,B85),1)+MOD(B85,1))) and it gives me the correct result :D
    Last edited by Violator; 11-14-2009 at 07:41 AM.

  12. #12
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

    Re: Calculate Hours Worked, Minus Weekends

    I think I put the final MOD addition the wrong way around (apologies)

    should read as

    Please Login or Register  to view this content.
    Of course in one of your examples you have a time falling on a non work day and as already mentioned this would throw the calcs out.

    Based on your values (ignoring weekend row - shown below as n/a) the output should be, by my reckoning:

    Please Login or Register  to view this content.
    If you will always have Suspend/Unsuspend times you can simplify the formula of course - I was not sure originally if this was to the case or not ?

    On an aside, you state 45 hours for the last record, it should be 48, no ? 3 hours + 9 hours on 10th, 24 hours on 11th and 12 hours on 12th --> 3 + 9 + 24 + 12 --> 48
    (the time period is actually 51 hours less 3 suspended)
    Last edited by DonkeyOte; 11-14-2009 at 07:57 AM.

  13. #13
    Registered User
    Join Date
    11-11-2009
    Location
    Chester, England
    MS-Off Ver
    Excel 2016
    Posts
    38

    Re: Calculate Hours Worked, Minus Weekends

    No last one should be 45:00 i.e. 45 hours. Ticket is open for 2 days (48), but suspended for 3 hours, so 48-3 = 45

    And unfortunately sometimes there won't be a suspend/unsuspend time. And it musn't count weekend time.

    But I think it's right now

  14. #14
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

    Re: Calculate Hours Worked, Minus Weekends

    Apologies I was referring to penultimate rather than last... for some reason I'd set both penultimate and last to be the same hence my 48.

+ 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