Converting Time Format

**VinceT** · 07-15-2015, 02:35 PM

Hello,

I currently have a notepad document auto-importing time into an excel worksheet. I am currently measuring the time it takes to download a page and it is usually measuring hundredths of a second in this format: 0.0# where # is a digit. For example:

0.03
0.50
12.50
3min5s

Sometimes the pages take a minute or more to load, when this happens the above occurs ( 3min5s )3min5s

What I want to do is somehow convert the cells that come in like the 3min5s to the format the same as the ones above it.

Any help would be greatly appreciated.

Regards,

Vince

**AlKey** · 07-15-2015, 02:47 PM

Try this formula

=IF(ISNUMBER(--A1),A1,LEFT(A1,FIND("min",A1)-1)+SUBSTITUTE(RIGHT(A1,2),"s","")/100)

Row\Col	A	B
1	0.03	0.03
2	0.5	0.5
3	12.5	12.5
4	3min5s	3.05

**VinceT** · 07-15-2015, 02:53 PM

You are awesome my friend. Thank you very much.

**AlKey** · 07-15-2015, 02:55 PM

You're welcome. Please don't forget to thank those who helped by clicking on Add Reputation * and please mark thread as "Solved" if your issue has been resolved. (Selecting Thread Tools-> Mark thread as Solved).

**VinceT** · 07-15-2015, 02:59 PM

Actually I found one little problem.

My original times were in hundredths of a second.

0.03 is actually 3/100 of a second

So the one's spot is actually one second. Could you tweak that formula a little to make the 3min5s to 185 seconds?

Thank you again for your help.

**AlKey** · 07-15-2015, 03:10 PM

Originally Posted by VinceT

Actually I found one little problem.

My original times were in hundredths of a second.

0.03 is actually 3/100 of a second

So the one's spot is actually one second. Could you tweak that formula a little to make the 3min5s to 185 seconds?

Thank you again for your help.

Sorry, I don't think I follow the logic here.

**VinceT** · 07-15-2015, 03:23 PM

Alkey,

Your formula worked up until the 3min5s.

This is what I have:

3.00 (3 seconds)
0.05 (less than 1 second)
3min5s

This is what I'm looking for.

3.00
0.05
185.00

Or if it's easier for you to convert everything into minutes that would be fine as well.

Your original formula converted 3min5s to 3.5 seconds.

Thank you again for your help.

Regards,
Vince

**AlKey** · 07-15-2015, 03:31 PM

Try this one

=IF(ISNUMBER(--A1),A1,LEFT(A1,FIND("min",A1)-1)*60+SUBSTITUTE(RIGHT(A1,2),"s",""))

**VinceT** · 07-15-2015, 03:36 PM

That one is perfect. Thank you again for your help. I'll mark your rep and have this marked solved! Thank you again!!!!!

Converting Time Format

LinkBack

Thread Tools

Rate This Thread

Display

Converting Time Format

Re: Converting Time Format

Re: Converting Time Format

Re: Converting Time Format

Re: Converting Time Format

Re: Converting Time Format

Re: Converting Time Format

Re: Converting Time Format

Re: Converting Time Format

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] converting a time format

Converting time in hh:mm:ss format to decimal format

[SOLVED] Converting an elapsed time in decimal number format to an actual time :S

Converting time format

Converting into a time format

Converting number format to time format

[SOLVED] Converting to Time Format

Bookmarks

Bookmarks

Posting Permissions