Subtract day and time w/o weekends or holidays

[Trudy]

Is there a way to subtract the day and time in two cells and not
include weekends and holidays. The weekends would start at 5:00 p.m.
Friday and end at 8:00 am Monday. Holidays would start at 5:00 p.m.
the day before the holiday and end on 8:00 am the day after the
holiday.

If the holiday falls on a Friday, the time that would be exluded would
be from Thursday at 5:00 p.m. to 8:00 a.m. Monday.

If the holiday falls on Monday, the time that would have to be exluded
would be 5:00 p.m Friday to 8:00 a.m. Tuesday.

What I am trying to do is below (no weekend example here)

Cell A2 = 08/06/08 2:00 pm
Cell A1 = 08/05/08 1:00 pm
Cell A2 - Cell A1 = 23 hours

Cell A2 = 08/01/2008 4:00 P.m.
Cell A1 - 08/04/2008 9:30 a.m.
Cell A2 - Cell A1 = 2.5 hours (Because this is a weekend.)

The end result would be the number of hours or days with fractions of
hours with weekends excluded.

I am trying to use only working business days. I looked at the
worksheet functions and couldn't figure out to nest them together. I
think this may have to be a program in VBA and my skills don't come
anywhere close to this. Right now I am doing it manually and it is
taking forever. I am on row 45 of 832.

I am hoping to not have to look up holidays and put them in manually.
Maybe Excel could do this??

Thanks for any help!!!


kajero
View Public Profile
Send a private message to kajero
Find all posts by kajero
Add kajero to Your Buddy List

 

[ryguy7272]

Give this a try:
=(NETWORKDAYS(A28+1,A29-1)+1-MOD(A28,1)+MOD(A29,1))-16/24

I used 16/24 at the end because I assumed two days over the weekend, with
normal 8 hr. work day, per day, per 24 hr. period. Can someone else confirm?

Regards,
Ryan--

 

[Trudy]

After I posted this, A programmer wrote this formula for me and it
works beautifully once the Column and Rows are revised to match what I
need.

Assuming start time/date in A2 and end time/date in B2 and a list of
holiday dates in F1:F10 (you can make this as big as you want but
should be a single column) then you can try this formula in C2

=SUMPRODUCT((WEEKDAY(A2+ROW(INDIRECT("1:"&ROUND((B2-A2)*1440,0)))/
1440-1/2880+1-"17:00",2)<6)*(WEEKDAY(A2+ROW(INDIRECT("1:"&ROUND((B2-
A2)*1440,0)))/1440-1/2880-"08:00",
2)<6)*ISNA(MATCH(INT(A2+ROW(INDIRECT("1:"&ROUND((B2-A2)*1440,0)))/
1440-1/2880+1-"17:00"),F1:F10,0))*ISNA(MATCH(INT(A2+ROW(INDIRECT("1:"&ROUND((B2-
A2)*1440,0)))/1440-1/2880-"08:00"),F1:F10,0)))/1440

format C2 as [h]:mm

Note: it's not the most "efficient" formula, it might calculate a
little slowly, I suggest you try it on a limited number of rows
initially

The above formula won't work for ranges over approx 45 days