Excel 2007 : Automating a Datedif formula or another that would make my life easier!!

**Icehockey44** · 02-22-2012, 05:57 AM

Hi All,

looking for your help yet again!!!

I have a machine base of around 4000, and have 2 1/2 years worth of breakdown history. I am needing to find the amount of days between each machines breakdowns (to work out the MTBF).

I am currently doing this in a very manual way, by pivoting 2 months worth of data, then datedif'ing to obtain the days between each call logged. So on each occassion where there are multiple failures, I have to manually move the formula points for each of the machine/day instances, and as you can imagine this is taking me ages.

I have attached an example.

Do any of you know or can think of a quicker way of doing this, as i have to do 2 1/2 years worth, my eyes are goning to go 'POP' very soon!!!

Looking forward to yur help.

Cheers

Donna

**Bob Phillips** · 02-22-2012, 06:38 AM

Your data is hard to understand, can you explain it?

**Icehockey44** · 02-22-2012, 07:01 AM

sorry Bob,

Across the top are the machine numbers, along the side are the dates that Breakdown calls have been logged. the 1's in the grid are where that machine has logged a breakdown call, sometimes 2 calls will be logged on the same day for the same machine.

I use a datedif formula to work out the days between calls logged on a single machine. If you double click on Cells C52 you will see a Blue and Green box appear on the dates that corrispond for that machines breakdowns. I then copy this formula to the next machine that has more than 1 call logged to it in the 2 month time period. double click the formula again, then amend where the formulas are looking to, to work out the days between calls logged for that new machine.

this is a very manual process and was wondering if there was a quicker way of doing this.

i hoe this makes a bit more sense.

Thansk for looking

Donna

**Icehockey44** · 02-24-2012, 06:56 AM

Hi All,

No luck with above, and the manual way is doing my head in. So i have decided to see if it can be done another way than pivoting.

I have attached a list of Machines number that have logged calls, and the date that those calls were logged.

some machines have multiple calls, other only 1. Is there a way that I can list all the instamces each machine has logged a call, and how many days it was between each logged call for that machine.

Scenario 1 Machine number 1 may have logged 3 calls in 1 month, having 3 days between call 1 and 2, then 10 days between call 2 and 3.

I need this by month, so if a call for a machine was logged in Jan and the next call was logged in Feb I still need this counted in the jan totals.

i hope this makes sense.

cheers

Donna

**daddylonglegs** · 02-24-2012, 07:36 AM

Can you indicate what the results should be, e.g. you have no data shown for previous or subsequent months so for machine 1 what would be MTBF for Jun, are you just dividing days in the month by the number of failures?

**Icehockey44** · 02-24-2012, 08:08 AM

Hi Daddylonglegs,

the formula I have been using so far (well 2 of them, as not sure which is correct!!)

a) No of days (in the month) / No of Multiple failures <28 days (between each call for the month)

So based on the example, June has 30 days / 6 multiple failures answer being 5 days

b) No of machines (that logged a call in the month) * No of days ( in the month) / No of Multiple failures <28 days (between each call for the month)

so based on that example , 10 machines * 30 days / 6 multiple failures answer being 50 days

I hope this makes sense!!!

**daddylonglegs** · 02-24-2012, 08:29 AM

OK, from your original question I inferred that you wanted to get a MTBF for each different machine - is that not the case?

If you are looking at overall MTBF then I agree with shg's comment in your previous question - surely you would divide the total number of machine days (for all machines whether they break down or not) by the total number of failure in the month, so in shg's example with 200 machines in total in a 31 day month 10 failures will give you (31*200)/10=620

How do you get 6 for your example - which ones are you counting? Is 28th June included, presumably for June you need to take data from the last few days of May too?

**Icehockey44** · 02-24-2012, 08:49 AM

well I need the MBTF per month, but am working it out by individual machine.

On the 2nd example attached, I only need to count as Multiple failures those machines that have logged a call with 28 days of a previous call, so in june we have 10 logged calls, but only 6 on them have a count of 28 days or less formt eh previosu call logged to that machine number.

any call that was logged in may then the next call for that machine was logged in June, it will only count if it is within 28 days, and would go into Mays stats.

i hope this helps