calculate availability

**daanyal** · 09-20-2007, 05:46 AM

I want to calculate the availability of 2000 parking machines, I have a sheet with start- and endtimes of failures of the machines.
for example:

Start-time failure End-time failure
15th September 13:00 16th September 11:30

so the failure has lasted 22:30 hours, but the machine only has to be available between 9:00 and 19:00 from monday till saturday. So now i want to filter out the hours outside these range. but how do i do this??
for my example it would have to be like this:

13:00 - 19:00 = 6hours
9:00 - 11:30 = 2:30 hours

so the machine has been unavailable for 8:30 hours.

I hope somebody can help me with this...

Danny

**daddylonglegs** · 09-20-2007, 07:56 AM

Hello daanyal,

If we're talking about 2007 then 16th September is a Sunday so I presume that isn't a valid example but....

Assuming your start time and date is in A2 and end time and date in B2 and that both of these times/dates always fall between 09:00 and 19:00 Mon-Sat then this formula will give the hours

=(INT(B2)-INT(A2)-1-INT((1-WEEKDAY(B2)+INT(B2)-INT(A2))/7))*("19:00"-"09:00")+MOD(B2,1)-MOD(A2,1)

format result cell as [h]:mm

...or could you have failures which start on Sundays or in the evening?

**daanyal** · 09-20-2007, 08:28 AM

thanx for your reaction

first, a failure can start on a sunday or in the evening
second, I tried your formula:

=(INT(B2)-INT(A2)-1-INT((1-WEEKDAY(B2)+INT(B2)-INT(A2))/7))*("19:00"-"09:00")+MOD(B2,1)-MOD(A2,1)

but i get an error on the part (B1,2)

would you know what can be the problem??

edit: both (B2,1) and (A2,1) cause a problem and i can't find what it is.

**daddylonglegs** · 09-20-2007, 09:10 AM

There is no part (B1,2)...if you mean (B2,1) then that probably means you have to replace the commas in the formula with semi-colons, i.e. ; if you have a non-English version of Excel.

I haven't got much time right now but I can look at amending the formula for start times at anytime. Can failure finish outside working hours too?

**daanyal** · 09-20-2007, 09:14 AM

indeed I have a Dutch version of excel, i replaced it and now it accepts the formula but i get #name? in this cell, I have changed the cell properties to [h]:mm but that does not help, also i have the analysis toolpak installed.

and yes, failure can finish outside working hours to.

edit: I´ve put an example file in the attachment

**daddylonglegs** · 09-20-2007, 08:37 PM

You can use this formula in D2 copied down

=(INT(B2)-INT(A2)-1-INT((1-WEEKDAY(B2)+INT(B2)-INT(A2))/7))*5/12+IF(WEEKDAY(B2)=1;19/24;MEDIAN(MOD(B2;1);3/8;19/24))-IF(WEEKDAY(A2)=1;3/8;MEDIAN(MOD(A2;1);3/8;19/24))

format as [h]:mm as before

using your example I got 14:28 in row 2, 32:10 in row 4

There are no Analysis ToolPak functions, if you get #NAME? error that's either because a space has crept in (there should be no spaces in the above formula) or you've misspelt a function [do you need to replace English function names with Dutch?]