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

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

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

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


.

Excel time conversion - Please help!!
 

On Fri, 23 Oct 2009 06:17:01 -0700, Sam J <Sam
wrote:

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

Sam,

That process will only change what is displayed. It will NOT change what is
stored. And any math performed on the data will act on the value stored, and
this may lead to unexpected results.

Consider the following

Format A1:A3 as h:mm:ss

then enter

A1: 4:33:07.4
A2: 4:33:07.4
A3: =A1+A2

The cells will display:

4:33:07
4:33:07
9:06:15


whereas you would probably want A3 to display

9:06:14
--ron

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