Time Stored as Text

  • Thread starter Thread starter Nasir
  • Start date Start date
N

Nasir

Hello All,

I import data from an external source which brings in several columns
* rows of data as time logs in Excel 2007

For Eg:

1 – this is 1 minute
1:01 – this is 1 minute and 1 second
1:35:46 – this is 1 hour 35 minutes and 46 seconds

I looked through the groups for thread “numbers stored as text” and
tried using Paste Special. Following shows the original data and the
results:

ORIGINAL
1
1:01
1:35:46

RESULTS after Paste Special is shown below (I have tried both using 0
(Add) and 1 (Multiply)) - both operation gives the same result

1
0.042361
0.066505

Results after formatting the cell as time

00:00:00
01:01:00
01:35:46

As can be seen results are not correct
I need a solution which should convert the following
1
1:01
1:35:46

To this
0:01:00
0:01:01
1:35:46

Any help would be greatly appreciated
Thanks
Nasir
 
Assuming your data starts in A1, put this in B1:

=VALUE(IF(LEN(TRIM(A1))<6,"0:","")&TRIM(A1)&IF(LEN(TRIM(A1))<3,":
0",""))

Format the cell as Time, and then copy down as far as you need to.

Hope this helps.

Pete
 
Assuming your data starts in A1, put this in B1:

=VALUE(IF(LEN(TRIM(A1))<6,"0:","")&TRIM(A1)&IF(LEN(TRIM(A1))<3,":
0",""))

Format the cell as Time, and then copy down as far as you need to.

Hope this helps.

Pete









- Show quoted text -
Click to expand...

Super! Works like a charm
Thanks a lot
 
Back
Top