Excel 2007 : Working Hrs, Days, Weekends, Holidays calculator

**Bazzzer** · 09-01-2010, 07:22 AM

Hi, I have trawled and tried various answers, but unfortunately I have not managed to find one that solves my problem ....

I am trying to work out the time lapsesd where I have Col A as a start dd/mm/yy hh:mm:sec, Col B as end dd/mm/yyyy hh:mm:sec.... sounds simple untill I then want it to ignore non-working hours, weekends and holidays.

Where working Hours are 09:00 to 17:00 Monday to Friday excluding Statutary holidays.

Example:Start (friday) 30/08/2010 15:30 Ends (wednesday) 01/09/2010 11:45 ---- (where monday 31st is a Holiday) Result = 12hrs 15 mins

Any help would be greatly appreciated!
Bazzzer

**sweep** · 09-01-2010, 07:54 AM

Hi,

I think you will find your answer here

**DonkeyOte** · 09-01-2010, 07:56 AM

Your example is a little awry I think given 30th Aug is a Monday and Bank Holiday so result should be 10:45 rather than 12:15, no ?

If we assume you meant 27th rather than 30th and 30th (rather than 31st) as Bank Holiday then:

Please Login or Register  to view this content.

where

B2 is start datetime
C2 is end datetime
holidays is a named range containing public holiday dates (eg Mon 30th Aug)
(note you could store working start/end times in cells and reference accordingly if preferred)

The above formula is c/o of our resident date guru daddylonglegs

edit: I should add that the above is an extended version which should cater for possibility of either/and/or start & end points occurring outside of working window

**Bazzzer** · 09-01-2010, 10:32 AM

Thanks yes I was wrong with the dates! however I cannot get the named reference to work, I have created a colmun with the 8 holiday dates in a dd/mm/yyyy format, highlighted them then defined name holidays ok'd it then the result is #value!
Again sorry to be a simpleton....
Bazzzer

**DonkeyOte** · 09-01-2010, 10:48 AM

It's not clear which approach you opted for - perhaps you could post a sample file ?

**Bazzzer** · 09-01-2010, 10:52 AM

Sorry, I coppied and pasted your formula and used the test dates of 27th and 1st to test it prior to building it into a working sheet, so I then added the named reference as described...
Hope that clarifies
Bazzzer