Use time data from an exported report in a formula - not working!

**psgolfer32381** · 12-19-2012, 09:59 AM

I have a machine that outputs data in a pretty raw format. Two of the columns are a start time and end time of a project. The format is "dd-mm-yy hh:mm:ss" for both. I am trying to insert a column that determines the length of time. I would think I could subtract one column from the other, but it's not working. I have tried clearing out all of the dates, leaving just the time. I have tried converting the time from general to number to time, etc. - not working. I have tried eliminating the seconds, I have tried doing many different things, and I always get the same result - "#value!". These are HUGE lists of info, so figuring this info one by one is really not feasible. Does anybody know what's up with this or how to get around it please?

Thanks
Michael

**Jakobshavn** · 12-19-2012, 10:17 AM

Post an attached workbook containing a small example of your data.

**psgolfer32381** · 12-19-2012, 10:21 AM

Sorry. Here is a portion of just the data I'm talking about.

**Jakobshavn** · 12-19-2012, 10:40 AM

Your data was actually text. See the attached.

**psgolfer32381** · 12-19-2012, 11:04 AM

I have no idea what it is you did there... Would you mind explaining all the "=DATE(MID(A2,7,4),MID(A2,4,2),LEFT(A2,2))+TIME(--MID(A2,12,2),--MID(A2,15,2),--RIGHT(A2,2))" stuff please? I would really appreciate it!

**Jakobshavn** · 12-19-2012, 11:14 AM

Sure................I treated all the material in columns A and B as simple strings of text. Dates and times can be added/subtracted, strings can not.

Cols C and D convert cols A and B into "real" date/times that can be manipulated by formulas. For example the =DATE() function makes at "real" date like:

=DATE(2012,12,25)

similar with the =TIME() function.

I used the =MID() functions to gather pieces of the strings to feed the DATE/TIME functions.

Column E performs the simple math with a reasonable formatting.