Excel 2007 : Calculating working hours between dates including weekends

Excellant worked a treat, as always.

Thanks for that, I had a feeliong from the other ones that i've done that i had forgotten something important.

Cheers

Donna

Daddylonglegs,

I am trying to add to your reputation via the *, but it won't let me!!! Sorry

Hi All,

is the above formula able to be adapted to accomodate different working hours on the weekend?

IE

Mon to Fri 08:00:00 to 17:30:00
Sat 09:00:00 to 16:00:00
Sun 09:00:00 to 14:00:00

the original example solution should read 13hours and 39Mins.

Cheers

Donna

Right, I have nearly go there, thanks to a post from here (history)

http://www.mrexcel.com/forum/showthr...46#post2113046

I now need to amend the Holiday list, which is a list of bank holidays which this formula excludes, where as I want them to be included but with sunday business hours (9-2), with exception to 25/12/2012, where we do not offer a service.

this is the formula that I have got to work:

=SUMPRODUCT(INT((WEEKDAY(A12-A2:A8)+INT(B12)-INT(A12))/7),D2:D8)-SUMPRODUCT((F2:F10>A12)*(F2:F10<B12),LOOKUP(WEEKDAY(F2:F10),A2:D8))+MOD(B12,1)-MOD(A12,1)+LOOKUP(WEEKDAY(A12),A2:B8)-LOOKUP(WEEKDAY(B12),A2:C8)

Where
A12 = Example Start Date/time
B12 = Example End Date/time
A2:A8 = Day of the week (1->7 Sunday to Saturday)
D2:D8 = Number of Business Hours worked by day
F2:F10 = Bank Holiday list
B2:B8 = Business Hours by Day
C2:C8 = Business Finish Hours By Day

Business Normal working Hours
Mon-Fri 08:30 - 17:30
Sat 09:00 - 16:00
Sun 09:00 - 14:00

Bank holiday business hours
09:00 - 14:00

I have attached an example for you to play with. Although on this example I have added columns next to the Holidays data with the Start/end and total hours, which Have not been used in the successful formula.

I hope this makes sense.

I look forward to your help

Cheers

Donna

any ideas as to how this can be achieved???