Summing duration in different formats

[NickyClemo]

Hi!

I'm a relative novice on excel but trying to do something fairly complicated with time (as a duration) which has got me completely stumped - would really appreciate any help people could give me.

I've copied and pasted my spreadsheet of data to help you see what my issues are. The data is copied and pasted from an xmls sheet and is used as raw data to populate other sheets in my work book. The problem I have is the way in which Excel deals with time. What I need to do is first extract only the data I want from this table into a clean work sheet i.e. I take only the date from column A and format Columns E and H into time (57:30:55 - US). I take all other information in the table (all columns A to H) as it is with no formatting changes etc.
Columns E and H are durations i.e. how long a process has been stopped for due to a specific fault.

I then have a third sheet which performs a SUMIF calculation to tell me how long the turbine has been stopped for in relation to each fault code (column B). To make this trickier column B contains spaces and I can't seem to find the right custom format to deal with this (raw data structure has just changed last month, previously columns B and C were in 1 column which made picking things up that much less fidly!). For a novice like me anyway!


As you can see at row 50 my problem comes when I have days, hours, minutes and seconds as custom formatting the cells to d.hh:mm:ss only goes so far to solving my problem.
What I need is a simple, repeatable process (I have to download data each quarter) that allows me to do some simple analysis and reduces the probability of carrying errors. I'm not greatly advanced on excel (trying to get work to stump up for a course) and neither is anyone else in my office so I need a spreadsheet that I understand and can therefore fix if anything goes wrong.


I don't mind having a few extra columns which I can then hide but the key for me is that this table is not modified in any way (the data I download each quarter is from the beginning of time each time), the second sheet allows me to get the data into the format and structure required to perform calculations with and the third spreadsheet allows me to analyse my data and create charts.


That's it in a nutshell, would be massively and eternally grateful to anyone who can find a simple and elegant solution to my problem!
Thank you!!

[TABLE="width: 1469"]
<colgroup><col><col><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD]Time[/TD]
[TD]Status[/TD]
[TD]Status text[/TD]
[TD]T[/TD]
[TD]Time diff.[/TD]
[TD]Information[/TD]
[TD]Information text[/TD]
[TD]Time diff.[/TD]
[/TR]
[TR]
[TD]16/05/2013 08:20:53[/TD]
[TD]0 : 0[/TD]
[TD]Turbine operational[/TD]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]16/05/2013 08:17:16[/TD]
[TD]0 : 1[/TD]
[TD]Turbine starting[/TD]
[TD]1[/TD]
[TD]00:03:37[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]16/05/2013 08:17:14[/TD]
[TD]0 : 2[/TD]
[TD]Turbine operational[/TD]
[TD]1[/TD]
[TD]00:00:02[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]16/05/2013 07:00:03[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]0 : 10[/TD]
[TD]Information / Warnings[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]16/05/2013 07:00:02[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]58 : 1[/TD]
[TD]Fault lubrication system : Grease reservoir empty (90)[/TD]
[TD]00:00:01[/TD]
[/TR]
[TR]
[TD]16/05/2013 06:24:50[/TD]
[TD]2 : 1[/TD]
[TD]Lack of wind : Wind speed to low[/TD]
[TD]1[/TD]
[TD]01:52:24[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]16/05/2013 03:06:23[/TD]
[TD]0 : 0[/TD]
[TD]Turbine operational[/TD]
[TD]1[/TD]
[TD]03:18:27[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]16/05/2013 03:04:39[/TD]
[TD]0 : 1[/TD]
[TD]Turbine starting[/TD]
[TD]1[/TD]
[TD]00:01:44[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]16/05/2013 03:04:37[/TD]
[TD]0 : 2[/TD]
[TD]Turbine operational[/TD]
[TD]1[/TD]
[TD]00:00:02[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]16/05/2013 02:33:33[/TD]
[TD]2 : 1[/TD]
[TD]Lack of wind : Wind speed to low[/TD]
[TD]1[/TD]
[TD]00:31:04[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]16/05/2013 01:34:21[/TD]
[TD]0 : 0[/TD]
[TD]Turbine operational[/TD]
[TD]1[/TD]
[TD]00:59:12[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]16/05/2013 01:32:49[/TD]
[TD]0 : 1[/TD]
[TD]Turbine starting[/TD]
[TD]1[/TD]
[TD]00:01:32[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]16/05/2013 01:32:47[/TD]
[TD]0 : 2[/TD]
[TD]Turbine operational[/TD]
[TD]1[/TD]
[TD]00:00:02[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]16/05/2013 01:07:16[/TD]
[TD]2 : 1[/TD]
[TD]Lack of wind : Wind speed to low[/TD]
[TD]1[/TD]
[TD]00:25:31[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]15/05/2013 07:00:03[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]0 : 10[/TD]
[TD]Information / Warnings[/TD]
[TD]23:59:59[/TD]
[/TR]
[TR]
[TD]15/05/2013 07:00:02[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]58 : 1[/TD]
[TD]Fault lubrication system : Grease reservoir empty (90)[/TD]
[TD]00:00:01[/TD]
[/TR]
[TR]
[TD]14/05/2013 07:00:02[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]0 : 10[/TD]
[TD]Information / Warnings[/TD]
[TD]1.00:00:00[/TD]
[/TR]
[TR]
[TD]14/05/2013 07:00:02[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]58 : 1[/TD]
[TD]Fault lubrication system : Grease reservoir empty (90)[/TD]
[TD]00:00:00[/TD]
[/TR]
[TR]
[TD]13/05/2013 07:19:04[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]0 : 10[/TD]
[TD]Information / Warnings[/TD]
[TD]23:40:58[/TD]
[/TR]
[TR]
[TD]13/05/2013 07:19:04[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]58 : 1[/TD]
[TD]Fault lubrication system : Grease reservoir empty (90)[/TD]
[TD]00:00:00[/TD]
[/TR]
[TR]
[TD]13/05/2013 07:19:02[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]0 : 0[/TD]
[TD][/TD]
[TD]00:00:02[/TD]
[/TR]
[TR]
[TD]13/05/2013 07:00:03[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]0 : 10[/TD]
[TD]Information / Warnings[/TD]
[TD]00:18:59[/TD]
[/TR]
[TR]
[TD]13/05/2013 07:00:02[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]58 : 1[/TD]
[TD]Fault lubrication system : Grease reservoir empty (90)[/TD]
[TD]00:00:01[/TD]
[/TR]
[TR]
[TD]13/05/2013 00:03:03[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]0 : 10[/TD]
[TD]Information / Warnings[/TD]
[TD]06:56:59[/TD]
[/TR]
[TR]
[TD]13/05/2013 00:03:03[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]232 : 102[/TD]
[TD]General information : Blade angle curve stored[/TD]
[TD]00:00:00[/TD]
[/TR]
[TR]
[TD]13/05/2013 00:03:00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]0 : 10[/TD]
[TD]Information / Warnings[/TD]
[TD]00:00:03[/TD]
[/TR]
[TR]
[TD]13/05/2013 00:03:00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]232 : 101[/TD]
[TD]General information : Power curve stored[/TD]
[TD]00:00:00[/TD]
[/TR]
[TR]
[TD]12/05/2013 07:00:03[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]0 : 10[/TD]
[TD]Information / Warnings[/TD]
[TD]17:02:57[/TD]
[/TR]
[TR]
[TD]12/05/2013 07:00:02[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]58 : 1[/TD]
[TD]Fault lubrication system : Grease reservoir empty (90)[/TD]
[TD]00:00:01[/TD]
[/TR]
[TR]
[TD]11/05/2013 07:55:45[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]0 : 10[/TD]
[TD]Information / Warnings[/TD]
[TD]23:04:17[/TD]
[/TR]
[TR]
[TD]11/05/2013 07:55:45[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]58 : 1[/TD]
[TD]Fault lubrication system : Grease reservoir empty (90)[/TD]
[TD]00:00:00[/TD]
[/TR]
[TR]
[TD]11/05/2013 07:55:43[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]0 : 0[/TD]
[TD][/TD]
[TD]00:00:02[/TD]
[/TR]
[TR]
[TD]11/05/2013 07:00:01[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]0 : 10[/TD]
[TD]Information / Warnings[/TD]
[TD]00:55:42[/TD]
[/TR]
[TR]
[TD]11/05/2013 07:00:01[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]58 : 1[/TD]
[TD]Fault lubrication system : Grease reservoir empty (90)[/TD]
[TD]00:00:00[/TD]
[/TR]
[TR]
[TD]10/05/2013 07:37:23[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]0 : 10[/TD]
[TD]Information / Warnings[/TD]
[TD]23:22:38[/TD]
[/TR]
[TR]
[TD]10/05/2013 07:37:23[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]58 : 1[/TD]
[TD]Fault lubrication system : Grease reservoir empty (90)[/TD]
[TD]00:00:00[/TD]
[/TR]
[TR]
[TD]10/05/2013 07:37:22[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]0 : 0[/TD]
[TD][/TD]
[TD]00:00:01[/TD]
[/TR]
[TR]
[TD]10/05/2013 07:00:03[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]0 : 10[/TD]
[TD]Information / Warnings[/TD]
[TD]00:37:19[/TD]
[/TR]
[TR]
[TD]10/05/2013 07:00:03[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]58 : 1[/TD]
[TD]Fault lubrication system : Grease reservoir empty (90)[/TD]
[TD]00:00:00[/TD]
[/TR]
[TR]
[TD]09/05/2013 07:00:03[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]0 : 10[/TD]
[TD]Information / Warnings[/TD]
[TD]1.00:00:00[/TD]
[/TR]
[TR]
[TD]09/05/2013 07:00:02[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]58 : 1[/TD]
[TD]Fault lubrication system : Grease reservoir empty (90)[/TD]
[TD]00:00:01[/TD]
[/TR]
[TR]
[TD]08/05/2013 07:38:06[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]0 : 10[/TD]
[TD]Information / Warnings[/TD]
[TD]23:21:56[/TD]
[/TR]
[TR]
[TD]08/05/2013 07:38:06[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]58 : 1[/TD]
[TD]Fault lubrication system : Grease reservoir empty (90)[/TD]
[TD]00:00:00[/TD]
[/TR]
[TR]
[TD]08/05/2013 07:38:02[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]0 : 0[/TD]
[TD][/TD]
[TD]00:00:04[/TD]
[/TR]
[TR]
[TD]08/05/2013 06:00:02[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]0 : 10[/TD]
[TD]Information / Warnings[/TD]
[TD]01:38:00[/TD]
[/TR]
[TR]
[TD]08/05/2013 06:00:02[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]58 : 1[/TD]
[TD]Fault lubrication system : Grease reservoir empty (90)[/TD]
[TD]00:00:00[/TD]
[/TR]
[TR]
[TD]07/05/2013 19:09:00[/TD]
[TD]0 : 0[/TD]
[TD]Turbine operational[/TD]
[TD]1[/TD]
[TD]8.05:58:16[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]07/05/2013 19:06:37[/TD]
[TD]0 : 1[/TD]
[TD]Turbine starting[/TD]
[TD]1[/TD]
[TD]00:02:23[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]07/05/2013 19:06:35[/TD]
[TD]0 : 2[/TD]
[TD]Turbine operational[/TD]
[TD]1[/TD]
[TD]00:00:02[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]07/05/2013 18:30:56[/TD]
[TD]2 : 1[/TD]
[TD]Lack of wind : Wind speed to low[/TD]
[TD]1[/TD]
[TD]00:35:39[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]07/05/2013 18:27:21[/TD]
[TD]0 : 1[/TD]
[TD]Turbine starting[/TD]
[TD]1[/TD]
[TD]00:03:35[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]07/05/2013 18:27:19[/TD]
[TD]0 : 2[/TD]
[TD]Turbine operational[/TD]
[TD]1[/TD]
[TD]00:00:02[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]07/05/2013 18:02:11[/TD]
[TD]2 : 1[/TD]
[TD]Lack of wind : Wind speed to low[/TD]
[TD]1[/TD]
[TD]00:25:08[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]07/05/2013 06:00:02[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]0 : 10[/TD]
[TD]Information / Warnings[/TD]
[TD]1.00:00:00[/TD]
[/TR]
[TR]
[TD]07/05/2013 06:00:02[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]58 : 1[/TD]
[TD]Fault lubrication system : Grease reservoir empty (90)[/TD]
[TD]00:00:00[/TD]
[/TR]
[TR]
[TD]06/05/2013 09:55:52[/TD]
[TD]0 : 0[/TD]
[TD]Turbine operational[/TD]
[TD]1[/TD]
[TD]1.08:06:19[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]