# 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

Excel 2010 Tables
Count net working hours considering
night shift, weekends and holidays.
No \$A\$1 or R1C1 in formulas.
http://c3017412.r12.cf0.rackcdn.com/11_26_11d.xlsx
If you get *.zip, don't unzip, just rename *.xlsx

• 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.

• 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...