Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel time conversion - Please help!!
Hi
I have been using a computer programme for some research which exports data to excel in the format of hours:minutes:seconds:tenth seconds In order to perform my calculations I would like to round the data to hours:minutes:seconds and I can't seem to do it! Its driving me mad! Please help, I am not neeading the acuracy of tenth second and do not want to delete the tenth second out of each cell as there are thousands! PLease help Thank you very much Nicola EggHeadCafe - Software Developer Portal of Choice ASP.NET Cookies FAQ http://www.eggheadcafe.com/tutorials...okies-faq.aspx |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel time conversion - Please help!!
On Fri, 23 Oct 2009 04:28:56 -0700, Nicola Blackie wrote:
Hi I have been using a computer programme for some research which exports data to excel in the format of hours:minutes:seconds:tenth seconds In order to perform my calculations I would like to round the data to hours:minutes:seconds and I can't seem to do it! Its driving me mad! Please help, I am not neeading the acuracy of tenth second and do not want to delete the tenth second out of each cell as there are thousands! PLease help Thank you very much Nicola EggHeadCafe - Software Developer Portal of Choice ASP.NET Cookies FAQ http://www.eggheadcafe.com/tutorials...okies-faq.aspx You can use this formula in a "helper" column: (assuming your original data starts in A1) =ROUND(A1/TIME(0,0,1),0)*TIME(0,0,1) Or, if you have the Analysis Tool Pak installed; or if you are using Excel 2007+, you could use: =MROUND(A1,TIME(0,0,1)) Then fill down as far as necessary. Then copy Paste Special Values and you should be done. --ron |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel time conversion - Please help!!
If you've actually got the data in there as an Excel time, use
=MROUND(A1,1/24/3600) (and look up MROUND in Excel help if you have problems). If it's there as text, =LEFT(A1,LEN(A1)-2) if you want to keep it as text, or =--LEFT(A1,LEN(A1)-2) (and format appropriately) if you want it converting to time. -- David Biddulph <Nicola Blackie wrote in message ... Hi I have been using a computer programme for some research which exports data to excel in the format of hours:minutes:seconds:tenth seconds In order to perform my calculations I would like to round the data to hours:minutes:seconds and I can't seem to do it! Its driving me mad! Please help, I am not neeading the acuracy of tenth second and do not want to delete the tenth second out of each cell as there are thousands! PLease help Thank you very much Nicola EggHeadCafe - Software Developer Portal of Choice ASP.NET Cookies FAQ http://www.eggheadcafe.com/tutorials...okies-faq.aspx |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel time conversion - Please help!!
Depending on what version of excel you have could you not just change the
format of the cell so it only displays hours, minutes, seconds Format cells Numbers Tab Custom and either go down the list until you find hh:mm:ss or you could just type that in. This way if you hi-light the whole column all the cells will be changed at once as well. Hope this helps "David Biddulph" wrote: If you've actually got the data in there as an Excel time, use =MROUND(A1,1/24/3600) (and look up MROUND in Excel help if you have problems). If it's there as text, =LEFT(A1,LEN(A1)-2) if you want to keep it as text, or =--LEFT(A1,LEN(A1)-2) (and format appropriately) if you want it converting to time. -- David Biddulph <Nicola Blackie wrote in message ... Hi I have been using a computer programme for some research which exports data to excel in the format of hours:minutes:seconds:tenth seconds In order to perform my calculations I would like to round the data to hours:minutes:seconds and I can't seem to do it! Its driving me mad! Please help, I am not neeading the acuracy of tenth second and do not want to delete the tenth second out of each cell as there are thousands! PLease help Thank you very much Nicola EggHeadCafe - Software Developer Portal of Choice ASP.NET Cookies FAQ http://www.eggheadcafe.com/tutorials...okies-faq.aspx . |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel time conversion - Please help!!
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Thank you
Thank you so much!
This has been annoying me for so long! It was not in time at all it was text as you suspected -thanks Might use Focus more often now! Nicola David Biddulph wrote: If you have actually got the data in there as an Excel time, 23-Oct-09 If you have actually got the data in there as an Excel time, use =MROUND(A1,1/24/3600) (and look up MROUND in Excel help if you have problems). If it is there as text, =LEFT(A1,LEN(A1)-2) if you want to keep it as text, or =--LEFT(A1,LEN(A1)-2) (and format appropriately) if you want it converting to time. -- David Biddulph <Nicola Blackie wrote in message Previous Posts In This Thread: EggHeadCafe - Software Developer Portal of Choice A Low-Overhead SysLog Message Sender Class http://www.eggheadcafe.com/tutorials...yslog-mes.aspx |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel time conversion issue | Excel Discussion (Misc queries) | |||
Time conversion in Excel | Excel Worksheet Functions | |||
Excel Time Conversion | Excel Discussion (Misc queries) | |||
Anyone have a GPS Time conversion for excel | Excel Discussion (Misc queries) | |||
Excel Time Conversion | Excel Discussion (Misc queries) |