Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 1
Default How do I convert time as text 'mm:ss to time value in Excel

I have time stored as text in the format 'mm:ss (e.g. '25:15) Note
the leading apostrophe.
Every Excel function & format I try assumes the minutes are hours and the
seconds minutes. I can manually edit cell to 0:mm:ss to get the functionality
I need but have 2000 rows to edit...

Any ideas would be much appreciated.



  #2   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 2,389
Default How do I convert time as text 'mm:ss to time value in Excel

Try this:

=timevalue("0:"&right(a1,5))

Format as mm:ss

Regards,
Fred.

"Sholto" wrote in message
...
I have time stored as text in the format 'mm:ss (e.g. '25:15)
Note
the leading apostrophe.
Every Excel function & format I try assumes the minutes are hours and the
seconds minutes. I can manually edit cell to 0:mm:ss to get the
functionality
I need but have 2000 rows to edit...

Any ideas would be much appreciated.




  #3   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 5,934
Default How do I convert time as text 'mm:ss to time value in Excel

I think you can just do this...

=A1/60

and then format that as mm:ss

--
Rick (MVP - Excel)


"Fred Smith" wrote in message
...
Try this:

=timevalue("0:"&right(a1,5))

Format as mm:ss

Regards,
Fred.

"Sholto" wrote in message
...
I have time stored as text in the format 'mm:ss (e.g. '25:15)
Note
the leading apostrophe.
Every Excel function & format I try assumes the minutes are hours and the
seconds minutes. I can manually edit cell to 0:mm:ss to get the
functionality
I need but have 2000 rows to edit...

Any ideas would be much appreciated.





  #4   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 2,389
Default How do I convert time as text 'mm:ss to time value in Excel

That will work as long as the apostrophe shown by the OP is Excel's text
indicator. If there's actually a leading apostrophe in the cell, it results
in a value error.

Regards,
Fred.

"Rick Rothstein" wrote in message
...
I think you can just do this...

=A1/60

and then format that as mm:ss

--
Rick (MVP - Excel)


"Fred Smith" wrote in message
...
Try this:

=timevalue("0:"&right(a1,5))

Format as mm:ss

Regards,
Fred.

"Sholto" wrote in message
...
I have time stored as text in the format 'mm:ss (e.g. '25:15)
Note
the leading apostrophe.
Every Excel function & format I try assumes the minutes are hours and
the
seconds minutes. I can manually edit cell to 0:mm:ss to get the
functionality
I need but have 2000 rows to edit...

Any ideas would be much appreciated.






  #5   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 15,768
Default How do I convert time as text 'mm:ss to time value in Excel

Assuming all the entries in question follow the same format: 'mm:ss

Try this...

Select the range of cells in question
Goto the menu EditReplace
Find what: ' (enter an apostrophe)
Replace with: 0:
Replace All

Then format the cells in the Time format of your choice.

--
Biff
Microsoft Excel MVP


"Sholto" wrote in message
...
I have time stored as text in the format 'mm:ss (e.g. '25:15)
Note
the leading apostrophe.
Every Excel function & format I try assumes the minutes are hours and the
seconds minutes. I can manually edit cell to 0:mm:ss to get the
functionality
I need but have 2000 rows to edit...

Any ideas would be much appreciated.







  #6   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 5,934
Default How do I convert time as text 'mm:ss to time value in Excel

If that were the case (although I don't think the OP was indicating that),
then this should work...

=SUBSTITUTE(A1,"'","")/60

again, formatting the cell with mm:ss

--
Rick (MVP - Excel)


"Fred Smith" wrote in message
...
That will work as long as the apostrophe shown by the OP is Excel's text
indicator. If there's actually a leading apostrophe in the cell, it
results in a value error.

Regards,
Fred.

"Rick Rothstein" wrote in message
...
I think you can just do this...

=A1/60

and then format that as mm:ss

--
Rick (MVP - Excel)


"Fred Smith" wrote in message
...
Try this:

=timevalue("0:"&right(a1,5))

Format as mm:ss

Regards,
Fred.

"Sholto" wrote in message
...
I have time stored as text in the format 'mm:ss (e.g. '25:15)
Note
the leading apostrophe.
Every Excel function & format I try assumes the minutes are hours and
the
seconds minutes. I can manually edit cell to 0:mm:ss to get the
functionality
I need but have 2000 rows to edit...

Any ideas would be much appreciated.







Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Convert Text Time to Excel Time [email protected] Excel Discussion (Misc queries) 5 January 29th 07 05:43 PM
How do i convert unix time to Julian time in excel? Ken Excel Discussion (Misc queries) 1 December 14th 06 09:22 PM
In Excel, I want to convert Eastern time to pacific time dmunhall Excel Discussion (Misc queries) 4 April 1st 06 01:03 AM
convert time imported as text to time format for calculations batfish Excel Worksheet Functions 3 October 27th 05 11:24 PM
Convert Text Time to Time Jan Excel Discussion (Misc queries) 1 March 7th 05 07:05 PM


All times are GMT +1. The time now is 03:11 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"