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

ricochetrob

Board Regular
Joined
Aug 23, 2004
Messages
67
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
 

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.
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.
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,211,434
Messages
6,101,822
Members
447,758
Latest member
jojo3strada

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top