Time Stored as Text

Giganews Newsgroups
Subject: Time Stored as Text
Posted by:  Nasir (nasirkhan.dub…@gmail.com)
Date: Mon, 20 Dec 2010

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 =96 this is 1 minute
1:01 =96 this is 1 minute and 1 second
1:35:46 =96 this is 1 hour 35 minutes and 46 seconds

I looked through the groups for thread =93numbers stored as text=94 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

Replies