Totaling some hours but not others HELP!

[vanilla_bean_orange via OfficeKB.com]

I really hope some one can help me with this because my brain has given up.

I have constructed a work sheet which calculates the downtime of automatic
bollards in our city.

In my spread sheet you input the date and time that the bollard goes down and
then you input the date and time that the bollards starts functioning again.

At the moment my formula is this: =(D28-B28)+(D28<B28) D28 being time up and
B28 being time down. This works fine but the problem is that ive just been
told that sundays cannot be counted and on some sites the hours from 9.30am -
7.30am the next day cannot be counted as down time.

Is there a simple adaption to this formula that if I input the date and time
the system goes down and the date and time it comes up again, it will
calculate the amount of hours down but omit sundays or whatever hours I
specify.

Your help would be EXTREMELY appreciated.

 

[Bob Phillips]

=INT(D28)-INT(B28)-(MOD(B28,1)>MOD(D28,1))-SUMPRODUCT(--(WEEKDAY(ROW(INDIREC
T(INT(B28)&":"&INT(D28))))=7))&" days
"&TEXT((1-MOD(B28,1)+MOD(D28,1))*24,"0")&" hours"

will ignore Sundays

=INT(D28)-INT(B28)-(MOD(B28,1)>MOD(D28,1))-SUMPRODUCT(--(WEEKDAY(ROW(INDIREC
T(INT(B28)&":"&INT(D28))))=7))&" days
"&TEXT(MIN(MAX(0,(TIME(21,30,0)-MOD(B28,1))*24),14)+MIN(MAX(0,(MOD(D28,1)-TI
ME(7,30,0))*24),14),"0")&" hours"

will also work for 7:30-21:30

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[daddylonglegs]

Do B28 and D28 both contain times AND dates?

If so I don't see why you would need

=(D28-B28)+(D28<B28)

in what circumstances would D28 be smaller than B28

 

[daddylonglegs]

Assuming B28 and D28 contain both time and date and also assuming tha
these might be on Sundays this formula will give total hours excludin
any time on a Sunday

=INT(D28)-INT(B28)-INT((WEEKDAY(B28-1)+INT(D28)-INT(B28))/7)+IF(WEEKDAY(D28)=1,1,MOD(D28,1))-(WEEKDAY(B28)<>1)*MOD(B28,1)

format as [h]:m

 

[vanilla_bean_orange via OfficeKB.com]

This worked a treat thank you very much!!!

Can this be edited so I can specify which hours not to count on a weekday
aswell, say 730am til 930am?

Thank you to other suggestions aswell

Assuming B28 and D28 contain both time and date and also assuming tha
these might be on Sundays this formula will give total hours excludin
any time on a Sunday

=INT(D28)-INT(B28)-INT((WEEKDAY(B28-1)+INT(D28)-INT(B28))/7)+IF(WEEKDAY(D28)=1,1,MOD(D28,1))-(WEEKDAY(B28)<>1)*MOD(B28,1)

format as [h]:m

 

[daddylonglegs]

To exclude hours between 07:30 and 09:30 Monday to Saturday as well as
all Sunday hours

=(SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT("1:"&ROUND((D28-B28)*1440,0)))/1440+B28-1/2880)<>1),--(ABS(MOD(ROW(INDIRECT("1:"&ROUND((D28-B28)*1440,0)))/1440+B28-1/2880,1)-17/48)>1/24)))/1440

If you want to specify which times to exclude Monday to Saturday, start
time in E1 (e.g. 07:30) and end time in F1 (e.g. 09:30) and use formula

=(SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT("1:"&ROUND((D28-B28)*1440,0)))/1440+B28-1/2880)<>1),--(ABS(MOD(ROW(INDIRECT("1:"&ROUND((D28-B28)*1440,0)))/1440+B28-1/2880,1)-(E1+F1)/2)>(F1-E1)/2)))/1440

format again as [h]:mm

 

[vanilla_bean_orange via OfficeKB.com]

your a wonderful person

Thanks very much

To exclude hours between 07:30 and 09:30 Monday to Saturday as well as
all Sunday hours

=(SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT("1:"&ROUND((D28-B28)*1440,0)))/1440+B28-1/2880)<>1),--(ABS(MOD(ROW(INDIRECT("1:"&ROUND((D28-B28)*1440,0)))/1440+B28-1/2880,1)-17/48)>1/24)))/1440

If you want to specify which times to exclude Monday to Saturday, start
time in E1 (e.g. 07:30) and end time in F1 (e.g. 09:30) and use formula

=(SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT("1:"&ROUND((D28-B28)*1440,0)))/1440+B28-1/2880)<>1),--(ABS(MOD(ROW(INDIRECT("1:"&ROUND((D28-B28)*1440,0)))/1440+B28-1/2880,1)-(E1+F1)/2)>(F1-E1)/2)))/1440

format again as [h]:mm

 

[daddylonglegs]

I should have warned you that there is a restriction on the time period
from B28 to D28, the formula(s) I suggested in my last post won't work
for time periods above approx 45 days. If that causes a problem they
can be adjusted.....

 

[vanilla_bean_orange via OfficeKB.com]

It wont cause a problem, 31days is the most that will be recorded at anyone
time and even that extremely unlikely.

Thanks again!