Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Converting time :: Excell 2007
Hello, I am copying data from a table in a webpage and am having difficulty
in converting the minutes and seconds. Example 2836:24:00 is really 2836 minutes 24 seconds and who knows what the 00 is. I have tried [mm]:ss and that returns the real minutes as if calcualated in hours. Another example is 16:10 which is 16 minutes 10 seconds. I need to convert these to example to the folowing format and am lost as to how to do it.. 2836.4 Minutes and 16.17 minutes I welcom all advise and help.. Thanks Bob |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Converting time :: Excell 2007
On Dec 23, 2:41*pm, Bob wrote:
Hello, I am copying data from a table in a webpage and am having difficulty in converting the minutes and seconds. *Example 2836:24:00 is really 2836 minutes 24 seconds and who knows what the 00 is.. I have tried [mm]:ss and that returns the real minutes as if calcualated in hours. Another example is 16:10 which is 16 minutes 10 seconds. I need to convert these to example to the folowing format and am lost as to how to do it.. 2836.4 Minutes and 16.17 minutes I welcom all advise and help.. Thanks Bob Are those original formats always exactly like that? What you have is time displayed in text format, I assume. And you want to turn it into a regular number value, not a "time" as Excel defines it. So this is really just a "text-to-value" exercise. Here's a formula that covers both of those formats you gave, assuming that there are always 2 seconds digits: =VALUE(LEFT(A1,SEARCH(":",A1,1)-1))+(VALUE(MID(A1,SEARCH (":",A1,1)+1,2)/60)) =VALUE(LEFT(A1,SEARCH(":",A1,1)-1))+VALUE(RIGHT(A1,2)/60) |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Converting time :: Excell 2007
Oops, I left an extra mess in that post. Ignore that second formula.
This is the one that should work: =VALUE(LEFT(A1,SEARCH(":",A1,1)-1))+(VALUE(MID(A1,SEARCH (":",A1,1)+1,2)/60)) |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Converting time :: Excell 2007
You've got 2836 hours 24 minutes and 0 seconds, so if you want to convert to
an Excel time of 2836 minutes 24 seconds you want to divide by 60. Either use a formula =A2/60, or put 60 in a spare cell, copy it, select the data you want to convert, and use Edit/ Paste Special/ Divide. If you then want to convert to decimal minutes, you'd need to multiply by 24*60 and format the result as Number or General. If you want to do the whole operation in one go it sounds as if you just need to multiply by 24 and format the result as Number or General. -- David Biddulph "Bob" wrote in message ... Hello, I am copying data from a table in a webpage and am having difficulty in converting the minutes and seconds. Example 2836:24:00 is really 2836 minutes 24 seconds and who knows what the 00 is. I have tried [mm]:ss and that returns the real minutes as if calcualated in hours. Another example is 16:10 which is 16 minutes 10 seconds. I need to convert these to example to the folowing format and am lost as to how to do it.. 2836.4 Minutes and 16.17 minutes I welcom all advise and help.. Thanks Bob |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Converting time :: Excell 2007
Try this:
=ROUND(A1/60*1440,2) Format as General or Number -- Biff Microsoft Excel MVP "Bob" wrote in message ... Hello, I am copying data from a table in a webpage and am having difficulty in converting the minutes and seconds. Example 2836:24:00 is really 2836 minutes 24 seconds and who knows what the 00 is. I have tried [mm]:ss and that returns the real minutes as if calcualated in hours. Another example is 16:10 which is 16 minutes 10 seconds. I need to convert these to example to the folowing format and am lost as to how to do it.. 2836.4 Minutes and 16.17 minutes I welcom all advise and help.. Thanks Bob |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Converting time :: Excell 2007
Thanks this works like a charm.. Bob
"T. Valko" wrote: Try this: =ROUND(A1/60*1440,2) Format as General or Number -- Biff Microsoft Excel MVP "Bob" wrote in message ... Hello, I am copying data from a table in a webpage and am having difficulty in converting the minutes and seconds. Example 2836:24:00 is really 2836 minutes 24 seconds and who knows what the 00 is. I have tried [mm]:ss and that returns the real minutes as if calcualated in hours. Another example is 16:10 which is 16 minutes 10 seconds. I need to convert these to example to the folowing format and am lost as to how to do it.. 2836.4 Minutes and 16.17 minutes I welcom all advise and help.. Thanks Bob |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Converting time :: Excell 2007
You're welcome. Thanks for the feedback!
-- Biff Microsoft Excel MVP "Bob" wrote in message ... Thanks this works like a charm.. Bob "T. Valko" wrote: Try this: =ROUND(A1/60*1440,2) Format as General or Number -- Biff Microsoft Excel MVP "Bob" wrote in message ... Hello, I am copying data from a table in a webpage and am having difficulty in converting the minutes and seconds. Example 2836:24:00 is really 2836 minutes 24 seconds and who knows what the 00 is. I have tried [mm]:ss and that returns the real minutes as if calcualated in hours. Another example is 16:10 which is 16 minutes 10 seconds. I need to convert these to example to the folowing format and am lost as to how to do it.. 2836.4 Minutes and 16.17 minutes I welcom all advise and help.. Thanks Bob |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Converting Standard Time to Military Time in Excel | Excel Discussion (Misc queries) | |||
Excell functions for converting column data | Excel Worksheet Functions | |||
formula for converting military time to standard time, etc | Excel Discussion (Misc queries) | |||
Converting date/time serial values to cumulative time totals... | Excel Discussion (Misc queries) | |||
How to open MS Excell 2007 Sheet in MS Excell 2000??? | New Users to Excel |