I can't seem to figure out how to make this work, I need to find out what the
time between failure on a particular machine is. My spreadsheet is laid out
as follows:
Column B - Date
Column C - Time
Column H - Location (Cell 1, Cell 1A, Cell 1B)
Column I - Machine (Winder, Extractor....)

If i could see how much time (days, hours, mins) between entries on first on
Column H, then broken down further with Column I

Thank you

According to my understanding of your description, Column H and I contain
text that is not related to time.

http://www.contextures.com/xlfaqDat.html

has information on date/time math that you might find useful.

If not, try provideing an example of some of the data and what result you
are looking for.


"Maintenance" wrote:

I can't seem to figure out how to make this work, I need to find out what the
time between failure on a particular machine is. My spreadsheet is laid out
as follows:
Column B - Date
Column C - Time
Column H - Location (Cell 1, Cell 1A, Cell 1B)
Column I - Machine (Winder, Extractor....)

If i could see how much time (days, hours, mins) between entries on first on
Column H, then broken down further with Column I

Thank you

Date Time Cell Machine
10/16/2008 3:00pm Cell 1 Extractor
10/17/2008 7:00am Cell 1A Cure_Oven
10/18/2008 9:00am Cell 1A Resin_Handling
10/18/2008 8:00am Cell 1A Resin_Handling
10/17/2008 8:15pm Cell 1A Cure_Oven_Walking_Beams
10/17/2008 7:00pm Cell 1A Resin_Handling

As you see above, "Cell 1A" "Resin_Handling" has had 3 entries in the sheet,
what is the time between entries? The spreadsheet contains over 400 entries
and i would like the average between entries specific to Cell & Machine


"~L" wrote:

According to my understanding of your description, Column H and I contain
text that is not related to time.

http://www.contextures.com/xlfaqDat.html

has information on date/time math that you might find useful.

If not, try provideing an example of some of the data and what result you
are looking for.


"Maintenance" wrote:

I can't seem to figure out how to make this work, I need to find out what the
time between failure on a particular machine is. My spreadsheet is laid out
as follows:
Column B - Date
Column C - Time
Column H - Location (Cell 1, Cell 1A, Cell 1B)
Column I - Machine (Winder, Extractor....)

If i could see how much time (days, hours, mins) between entries on first on
Column H, then broken down further with Column I

Thank you

If this data was arranged in columns A through D starting in row 2 and not
exceeding row 7000, In column E row 2 enter:

=TEXT(AVERAGE(SUMPRODUCT(--($D$2:$D$7000=D2),--($C$2:$C$7000=C2),--(TIMEVALUE((LEFT($B$2:$B$7000,LEN($B$2:$B$7000)-2))&"
"&RIGHT($B$2:$B$7000,2))))),"d:[H]:mm")

And fill down to the end of your range.

It can be extended by changing the $7000s to the limits of your spreadsheet.

"Maintenance" wrote:

Date Time Cell Machine
10/16/2008 3:00pm Cell 1 Extractor
10/17/2008 7:00am Cell 1A Cure_Oven
10/18/2008 9:00am Cell 1A Resin_Handling
10/18/2008 8:00am Cell 1A Resin_Handling
10/17/2008 8:15pm Cell 1A Cure_Oven_Walking_Beams
10/17/2008 7:00pm Cell 1A Resin_Handling

As you see above, "Cell 1A" "Resin_Handling" has had 3 entries in the sheet,
what is the time between entries? The spreadsheet contains over 400 entries
and i would like the average between entries specific to Cell & Machine


"~L" wrote:

According to my understanding of your description, Column H and I contain
text that is not related to time.

http://www.contextures.com/xlfaqDat.html

has information on date/time math that you might find useful.

If not, try provideing an example of some of the data and what result you
are looking for.


"Maintenance" wrote:

I can't seem to figure out how to make this work, I need to find out what the
time between failure on a particular machine is. My spreadsheet is laid out
as follows:
Column B - Date
Column C - Time
Column H - Location (Cell 1, Cell 1A, Cell 1B)
Column I - Machine (Winder, Extractor....)

If i could see how much time (days, hours, mins) between entries on first on
Column H, then broken down further with Column I

Thank you

If the data was in different sheets or columns i would just have to adjust
the formula to accomodate for that, but when i tried it, it didn't work, and
when i set up the info as i sent it to you on a new sheet i still recieved
errors. Any ideas?


"~L" wrote:

If this data was arranged in columns A through D starting in row 2 and not
exceeding row 7000, In column E row 2 enter:

=TEXT(AVERAGE(SUMPRODUCT(--($D$2:$D$7000=D2),--($C$2:$C$7000=C2),--(TIMEVALUE((LEFT($B$2:$B$7000,LEN($B$2:$B$7000)-2))&"
"&RIGHT($B$2:$B$7000,2))))),"d:[H]:mm")

And fill down to the end of your range.

It can be extended by changing the $7000s to the limits of your spreadsheet.

"Maintenance" wrote:

Date Time Cell Machine
10/16/2008 3:00pm Cell 1 Extractor
10/17/2008 7:00am Cell 1A Cure_Oven
10/18/2008 9:00am Cell 1A Resin_Handling
10/18/2008 8:00am Cell 1A Resin_Handling
10/17/2008 8:15pm Cell 1A Cure_Oven_Walking_Beams
10/17/2008 7:00pm Cell 1A Resin_Handling

As you see above, "Cell 1A" "Resin_Handling" has had 3 entries in the sheet,
what is the time between entries? The spreadsheet contains over 400 entries
and i would like the average between entries specific to Cell & Machine


"~L" wrote:

According to my understanding of your description, Column H and I contain
text that is not related to time.

http://www.contextures.com/xlfaqDat.html

has information on date/time math that you might find useful.

If not, try provideing an example of some of the data and what result you
are looking for.


"Maintenance" wrote:

I can't seem to figure out how to make this work, I need to find out what the
time between failure on a particular machine is. My spreadsheet is laid out
as follows:
Column B - Date
Column C - Time
Column H - Location (Cell 1, Cell 1A, Cell 1B)
Column I - Machine (Winder, Extractor....)

If i could see how much time (days, hours, mins) between entries on first on
Column H, then broken down further with Column I

Thank you

A few ideas:
Because I saw Time in a header, I separated time from date when I set up the
data you sent. If you didn't do this, it would change the formula. So,
column A was dates, column B was times, Column C was Cell addresses and
column D was machines.

Also, I kept the time as text with the PM immediately following the time
(which would be separated by the LEFT(B2:B7000,LEN(B2:B7000)-2)&"
"&RIGHT(B2:B7000,2) part).

If you have an actual time instead of text, remove the
Timevalue(LEFT(B2:B7000,LEN(B2:B7000)-2)&" "&RIGHT(B2:B7000,2)) functions and
just have the B2:B7000 range.

Also, be sure that if you have a value table off in an unused portion of the
document, it does not enter the range A2:D7000 or you will be creating a
circular reference.

When creating the value table, copy and paste from the cells containing
text, or use the advanced filter tool to create your list to capture any
characters you can't easily detect (such as additional leading or trailing
spaces).

While we're on the topic and I'm examining this formula, I'm averaging the
time in this formula, but I'm not sure what an average of time tells you.
You mentioned times between before, should I adjust this to be the average of
diffrences between (for example on the Reisin Handler) 10/17/08 0700,
10/17/08 0900 and 10/18/08 0800? (2 hours + 23 hours / 2 = 12.5 hours)

Also, you can get rid of the TEXT( ,"d:[h]:mm") part if you format the
cells as date/time in d:[h]:mm format yourself, which would help if this is
not the end product and you still have to do math on the times, but would
only add to the time and labor involved in generating this if all you need is
to see the number.

"Maintenance" wrote:

If the data was in different sheets or columns i would just have to adjust
the formula to accomodate for that, but when i tried it, it didn't work, and
when i set up the info as i sent it to you on a new sheet i still recieved
errors. Any ideas?


"~L" wrote:

If this data was arranged in columns A through D starting in row 2 and not
exceeding row 7000, In column E row 2 enter:

=TEXT(AVERAGE(SUMPRODUCT(--($D$2:$D$7000=D2),--($C$2:$C$7000=C2),--(TIMEVALUE((LEFT($B$2:$B$7000,LEN($B$2:$B$7000)-2))&"
"&RIGHT($B$2:$B$7000,2))))),"d:[H]:mm")

And fill down to the end of your range.

It can be extended by changing the $7000s to the limits of your spreadsheet.

"Maintenance" wrote:

Date Time Cell Machine
10/16/2008 3:00pm Cell 1 Extractor
10/17/2008 7:00am Cell 1A Cure_Oven
10/18/2008 9:00am Cell 1A Resin_Handling
10/18/2008 8:00am Cell 1A Resin_Handling
10/17/2008 8:15pm Cell 1A Cure_Oven_Walking_Beams
10/17/2008 7:00pm Cell 1A Resin_Handling

As you see above, "Cell 1A" "Resin_Handling" has had 3 entries in the sheet,
what is the time between entries? The spreadsheet contains over 400 entries
and i would like the average between entries specific to Cell & Machine


"~L" wrote:

According to my understanding of your description, Column H and I contain
text that is not related to time.

http://www.contextures.com/xlfaqDat.html

has information on date/time math that you might find useful.

If not, try provideing an example of some of the data and what result you
are looking for.


"Maintenance" wrote:

I can't seem to figure out how to make this work, I need to find out what the
time between failure on a particular machine is. My spreadsheet is laid out
as follows:
Column B - Date
Column C - Time
Column H - Location (Cell 1, Cell 1A, Cell 1B)
Column I - Machine (Winder, Extractor....)

If i could see how much time (days, hours, mins) between entries on first on
Column H, then broken down further with Column I

Thank you