Posts by korakora

    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

    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]