Hi!

I'm quite a novice with Excel but am trying to put together a relatively complicated spreadsheet at work and have run into a couple of problems with the way Excel views time, would be great if someone could help me out!

I have a spreadsheet of raw data which contains cells with the format xx:yy:zz and w.xx.yy.zz which represent time as duration. In other words a process that stopped working for 1 day, 16 hours, 53 minutes and 32 seconds would be recorded as 1.16:53:32. The same column of data also contains time periods where a process has stopped for less than a day, for example process x stopped working for 23 hours, 30 minutes and 15 seconds is recorded as 23:30:15.

I have no problem formatting my cells to show the xx:yy:zz format correctly as duration rather than a fixed point of time HOWEVER I need to be able to sum the data within this column and the only way I can see to do this is to use the formula RIGHT(text, [num_chars]) to extract all the data as xx:yy:zz (regardless of whether the cell contains the duration as xx:yy:zz or w.xx:yy:zz) so that can be summed. Then in a separate column I need to be able to extract the left most character when the raw data column contains the more than 8 characters so I pick up the w character in the formats above.

So my formula needs to ask "when cell x contains >8 characters, find the left character". Does anyone know what function I can use please?

Cut and paste of my columns of data below to help you see what I'm getting at.

RAW Data
07/05/2013 19:09:00 0 : 0 Turbine operational 1 8.05:58:16
07/05/2013 19:06:37 0 : 1 Turbine starting 1 00:02:23

Thank you in advance!