Formula to only return certain values in a cell when it contains more than 8 integers

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!

Excel stores dates as the number of elapsed days since some reference day (1st Jan 1900). So dates are always integers. Times are stored internally as fractions of a 24-hour day, so 12 noon will have the value 0.5, 6:00pm will be 0.75, and so on. As these are just numbers they can be added/subtracted in the normal way - formatting just changes the way they are displayed and doesn't affect the underlying numerical values.

Hence, assuming your different formats are still acting on time values, you should be able to just SUM them and then apply an appropriate format to the result.

Hope this helps.

Pete

Hi Pete,

Thank you for responding so quickly but unfortunately my data is a duration i.e. something that took place over a number of hours, hence I've formatted the cells into 37:30:55 time format and you're right I can sum my xx:yy:zz data sets no problem. The issue comes when my data has days in it too so d.hh:mm:ss. In this scenario even formatted in the 37:30:55 time format the sum function won't work.

So I've extracted all the rows in the column where the time is simply hh:mm:ss and now I need to extract the days from the rows which contain d.hh:mm:ss, convert them into the format hh:mm:ss (e.g. 1 day would convert to 23:59:59) and then sum the two columns. Does that make sense? It's very difficult to explain without being able to show someone by worksheet!

Nicky

Why don't you attach the workbook, then? Change any confidential information, and to attach a file you just click on Go Advanced, then scroll down to Manage Attachments, then Add files | Select files (navigate to your file and double-click it), then Done and Submit post.

Hope this helps.

Pete

Hi Pete,

worksheet attached. Thank you so much, this is really appreciated, I've started to go a bit mad looking up things in the Excel manual and drawing a blank!

Nicky

Not sure if it attached previously, trying again.

nicky

So where does the data in the yellow column come from? Is it already in that format when you get it? (Both types seem to be text values, even though one of them looks like hh:mm:ss). Do you want me to extract all the entries from the column D into another column (G ??) in the correct format, so you can then just add that column? I'm not sure what columns E and F are for, even though one is called time difference.

Pete

Yes the data is already in that format when I get it so then I convert it to 37:30:55 format and conditional format it.

You can ignore columns E and F as they're just data on error messages rather than the error itself.

Put this formula in G8:

=IF(D8="","",IF(ISNUMBER(FIND(".",D8)),LEFT(D8,FIND(".",D8)-1)+RIGHT(D8,LEN(D8)-FIND(".",D8)),D8*1))

Apply a Custom Format to that cell of:

d.hh:mm:ss

then copy/paste the cell down to G904.

That will display all the times in the same format, and will display okay as long as the time difference in column D does not exceed 31 days (none of yours seem to). The formula will calculate the numbers correctly - it's just that the display is limited to 31 days max using this format (above that it would show 1 month, 1 day and so on).

Although you can just use SUM now on the converted data to get the total, it will not be displayed correctly. To get around it, you can put this formula in G6:

=TEXT(INT(SUM(G8:G2000)),"0.")&TEXT(MOD(SUM(G8:G2000),1),"hh:mm:ss")

This splits the sum into its integer and fractional values, and then displays them in the same format.

The attached workbook shows this in operation.

Hope this helps.

Pete