Calculation net working hours when business end time is less than business start time

  • Hi,


    I have a scenario where i need to calculate net working hours between 2 dates, excluding weekends. The recognized working hours in a day(Mon to Fri) are from 09:00 AM to 01:30 AM (next day morning). If someone, works outside this window, those hours are not counted


    E.g 1 If i start on 28th Nov, 2011 at 9 AM and end on 29th Nov, 2011 at 9 AM - the net working hours should be 16.5 hrs
    E.g 2 If i start on 28th Nov, 2011 at 8 AM and end on 29th Nov, 2011 at 9 AM - the net working hours should be 16.5 hrs
    E.g 3 If i start on 28th Nov, 2011 at 10 PM and end on 29th Nov, 2011 at 9 AM - the net working hours should be 3.5 hrs
    E.g 3 If i start on 27th Nov, 2011 at 10 PM and end on 29th Nov, 2011 at 9 AM - the net working hours should be 16.5 hrs


    I have tried formula from below but it fails as the working hours end time (01:30 AM) is lesser than start time (09:30 AM)


    =IF(M3<>"",(NETWORKDAYS(G3,M3,$AW$2:$AW$15)-1)*($AY$2-$AX$2)+IF(NETWORKDAYS(M3,M3,$AW$2:$AW$15),MEDIAN(MOD(M3,1),$AY$2,$AX$2),$AY$2)-MEDIAN(NETWORKDAYS(G3,G3,$AW$2:$AW$15)*MOD(G3,1),$AY$2,$AX$2),"NA")


    where G3 = Start date
    M3= End Date
    $AW$2:$AW$15 = Holiday List
    $AY$2 = 01:30 AM
    $AX$2= 09:00 AM



    Any support on this would be very helpful


    Regards,
    Vinay
    [TABLE="width: 95"]

    [tr]


    [td][/td]


    [/tr]


    [/TABLE]

  • Re: Calculation net working hours when business end time is less than business start


    General theory is to add 1 if start is greater than finish as in:


    (AY2-AX2)+(AX2>AY2)


    The second half of the above evaluates as 1 (because it is true) so therefore it is the next day.


    Multiply by 24 to get elapsed hours


    ((AY2-AX2)+(AX2>AY2))*24


    HTH


    dr

  • Re: Calculation net working hours when business end time is less than business start


    Hi,


    herbsd7 - Thanks a lot for the immediate reply. Should have clarified that am using Excel 2007. Is there a way to do this in Excel 2007?


    Also, am not exposed to Excel 2010 tables and when i open your spreadsheet, i see NetMins function but am unable to see the formula syntax or the way it is calculated there. Am i missing something?


    Regards,
    Vinay

  • Re: Calculation net working hours when business end time is less than business start


    Compatible with Excel 2007.
    Expose yourself to Tables, Structured References,
    Defined Names and Name Manager.

  • Re: Calculation net working hours when business end time is less than business start


    Hey Herbds7,


    Yep, need to understand the same and am working on it. Have one quick question though. Am trying to understand the reason, but in the sheet sent by you, the first 2 records have num errors.



    Can't thank you enough for this super solution :)


    Regards,

  • Re: Calculation net working hours when business end time is less than business start


    To expose the problem, (and to learn The Method),
    you need to troubleshoot the error cell(s).
    Enter the constituent Defined Name(s) in the same row.
    Enter them as array formulas in multiple cells. Look for error values.
    If desperate, upload your current best effort.

  • Re: Calculation net working hours when business end time is less than business start


    Thanks a lot for the tip, Herbds7. Am able to move forward with being able to trouble shoot the same.


    Thanks again.

  • Re: Calculation net working hours when business end time is less than business start


    Hello Herb;


    I found this workbook and it has really given me a jumpstart, many thanks. I have a question regarding the Priority which references table 3: =LOOKUP([@PriorityTxt];Table3[P_Txt];Table3[Priority])
    All looks OK apart from Low Prios which report the value back as 2 when I am expecting 4.


    I've tested with excel 2010&13. I hope you can advise

  • Re: Calculation net working hours when business end time is less than business start


    Quote

    I found this workbook...


    Please insert the link to the unspecified workbook.
    The formula does not appear in the WS mentioned in this thread.
    I suggest you start a new thread.
    Read the rules on hijacking.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!