Time Calculations(Cont...)

**ExceltoLead** · 03-21-2011, 01:24 AM

In an earlier post daddylonglegs showed a great way of calculating time differences accommodating Working hours exclusive of weekends, would it be possible to change the weekdays taken into account e.g Saturday- Wednesday

The below link shows how to exclude specific days of the week without networkdays but does not calculate for working hours

http://www.cpearson.com/excel/betternetworkdays.aspx

Originally Posted by daddylonglegs
I do have a formula that accomodates start and/or end times outside the business hours, i.e.

=(NETWORKDAYS(A2,B2)-1)*(J$3-J$2)+IF(NETWORKDAYS(B2,B2),MEDIAN(MOD(B2,1),J$3, J$2),J$3)-MEDIAN(NETWORKDAYS(A2,A2)*MOD(A2,1),J$3,J$2)

Where A2, B2, J2 and J3 are as before. This formula doesn't take lunch into account, do you need that?

**DonkeyOte** · 03-21-2011, 04:04 AM

Given your revised working days (Sat - Wed) covers the same number of days as NETWORKDAYS default (Mon - Fri) you can simply adjust/offset the dates in the NETWORKDAYS calculation so as to account for move from Mon-Fri to Sat-Wed

Adapting daddylonglegs' formula from above:

Please Login or Register  to view this content.

**ExceltoLead** · 03-21-2011, 05:44 AM

Hi

Thanks for the quick response, using your provided formula i am getting the output

4:30:20

for below dates although its a Friday on the 18th and my weekdays are from Sat-Wed, it should be zero, any suggestions?

Start_Date End Date
3/18/2011 3:59 3/18/2011 12:30

J2=08:00
J3=16:00

**DonkeyOte** · 03-21-2011, 05:48 AM

With Start_Date & End Date in A2 & B2 respectively and start/end hours in J2 & J3 as outlined the formula should generate 0.

Please post a sample file to illustrate your result.

**ExceltoLead** · 03-21-2011, 06:21 AM

IT Works!!!

Apologies for the confusion, tried it on my colleagues system as mine was acting up, got the dreaded blue screen and just crashed, you're a lifesaver!