pulled in Time data (i.e. 037:02:42) won't calculate

[ricochetrob]

I have a column of Time (or at least it was before I pulled it from SAP into Excel) and it won't let me SUM it (or AVERAGE, or anything else).

I've got to figure out a way to get this field to be recognized as Time again (I think) in order to get it to calculate a SUM or AVERAGE.

Has anyone ever come across a problem like this before?

Thanks,
Rob

 

[T. Valko]

I have a column of Time (or at least it was before I pulled it from SAP into Excel) and it won't let me SUM it (or AVERAGE, or anything else).

I've got to figure out a way to get this field to be recognized as Time again (I think) in order to get it to calculate a SUM or AVERAGE.

Has anyone ever come across a problem like this before?

Thanks,
Rob

It's the leading 0 that's causing the problem.

Do all the entries have a leading 0? How about posting several representative samples.

 

[ricochetrob]

Yes, they all have a leading 0


001:08:08
002:08:49
003:00:41
003:01:30
004:03:48
005:00:30
005:06:59
006:04:43
006:05:27
006:06:59
008:06:36
008:06:44
009:03:17
010:00:14
010:05:18
011:01:28
012:06:22
012:08:08
013:00:50
014:03:17
016:06:38
019:06:54
019:08:18
020:04:22
024:03:18
028:00:04
030:06:38
037:02:42

 

[T. Valko]

Yes, they all have a leading 0


001:08:08
002:08:49
003:00:41
003:01:30
004:03:48
005:00:30
005:06:59
006:04:43
006:05:27
006:06:59
008:06:36
008:06:44
009:03:17
010:00:14
010:05:18
011:01:28
012:06:22
012:08:08
013:00:50
014:03:17
016:06:38
019:06:54
019:08:18
020:04:22
024:03:18
028:00:04
030:06:38
037:02:42

This worked for me in Excel 2002.

Select the range of cells in question.

Goto Data>Text to Columns
Select: Fixed width
Click: Next

In the Data preview window, use your mouse and click between the leading 0 and the next character. This will place a line between those characters that shows where the data will be split.

Click: Next

In Step 3 of the userform, in the Data preview window, the column of leading zeros will be highlighted.

Under Column data format, select: Do not import
Click: Finish

That should get rid of the leading zeros and convert the entries to true time values.

You may have to format as [h]:mm:ss.

 

[ricochetrob]

frickin' awesome; thank you !!!!!

 

[T. Valko]

frickin' awesome; thank you !!!!!

You're welcome!