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.

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/200602/1

=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)

"vanilla_bean_orange via OfficeKB.com" <u16604@uwe wrote in message
news:5b3a24637d44e@uwe...
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.

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/200602/1

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
------------------------------------------------------------------------
daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486
View this thread: http://www.excelforum.com/showthread...hreadid=507186

Assuming B28 and D28 contain both time and date and also assuming that
these might be on Sundays this formula will give total hours excluding
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]:mm


--
daddylonglegs
------------------------------------------------------------------------
daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486
View this thread: http://www.excelforum.com/showthread...hreadid=507186

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

daddylonglegs wrote:
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

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/200602/1

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
------------------------------------------------------------------------
daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486
View this thread: http://www.excelforum.com/showthread...hreadid=507186

your a wonderful person

Thanks very much

daddylonglegs wrote:
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


--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/200602/1

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.....


--
daddylonglegs
------------------------------------------------------------------------
daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486
View this thread: http://www.excelforum.com/showthread...hreadid=507186

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

Thanks again!

daddylonglegs wrote:
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.....


--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/200602/1