Posts by korakora

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

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.

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

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,

• Difference between date/time stamp with business hours only

Re: Difference between date/time stamp with business hours only

Hi,

Pls. find what you require - calculate time only within business working window - assuming weekends do not count and with additopnal feature of excluding holidays. You can just extend this to add more incidents

Regds

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

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

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