Below formula should work for the cell in column C. You may need to turn on
the analysis toolpak Excel Addin for this to work. Networkdays will also
allow you to define holidays as an option (i.e.
NETWORKDAYS(A2,B2,RANGE_HOLIDAYS)). S_TIME and E_TIME are just named ranges
that I used to enter start and end time once instead of repeating in the
formula, the will need to be formated as time for this to work. You may need
a few more if statements to make sure that the start and end time of the job
is within office hours, else the calculating will start substracting time.

Hope this helps,

felix

=IF(NETWORKDAYS(A2,B2)=1,B2-A2*24,IF(NETWORKDAYS(A2,B2)=2,(E_Time-(A2-ROUNDDOWN(A2,0))+((B2-ROUNDDOWN(B2,0))-S_Time))*24,(NETWORKDAYS(A2,B2)*(E_Time-S_Time))*24+(E_Time-(A2-ROUNDDOWN(A2,0))+((B2-ROUNDDOWN(B2,0))-S_Time))*24))

"Altec101" wrote:


Hello Folks,

I need to create a macro that will be able to determine the total
amount of time from 8:30am till 4:30pm between two columns (column A
holds the start time and column B holds the close time) and have it
insert into column C the total amount of time calculated between two
dates and times between the hours of 8:30am and 4:30pm Monday through
Friday, not including holidays.
The start date could be something like 16/05/2006 1:30:02 PM and the
end date could be 18/05/2006 3:30:02 PM, 2 days from the start time. I
need the total amount of time there was from 1:30pm till 4:30pm on
16/05/2006 the total amount of time for the next day between 8:30 and
4:30 on 17/05/2006 and the total amount of time on the closing date
between 8:30 and 3:30.(3:30 being the time the report was closed).
I only need the times between business hours for each day (which are
8:30am to 4:30pm) from Monday to Friday and have it exclude holidays
too.

Example #2: If a report was created after 4:30pm(start time) and not
closed until 1:30pm(closed time) the next day, the only time counted
would be from 8:30 until 1:30 the following day, because the only time
that falls between 8:30 to 4:30 time is on the following day.

Example#2: If a report was created Monday at 11:30am (start time) and
not closed until 3:30pm (closed time) Wednesday, then the time counted
would be from 11:30am until 4:30pm same day the report was created,
Tuesday from 8:30am to 4:30pm and Wednesday from 8:30am until 3:30pm
because the report was open from Monday to Wednesday and only the time
between 8:30 and 4:30 was counted for them days. :)


--
Altec101
------------------------------------------------------------------------
Altec101's Profile: http://www.excelforum.com/member.php...o&userid=34539
View this thread: http://www.excelforum.com/showthread...hreadid=543329