Excel time conversion - Please help!!

[Nicola Blackie]

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/tutorial...388-89e5-fce33d725aa7/aspnet-cookies-faq.aspx

 

[Ron Rosenfeld]

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/tutorial...388-89e5-fce33d725aa7/aspnet-cookies-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

 

[David Biddulph]

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.

 

[Sam J]

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

 

[Ron Rosenfeld]

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

 

[Nicola Blackie]

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, us
=MROUND(A1,1/24/3600) (and look up MROUND in Excel help if you hav
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 i
converting to time
-
David Biddulp

<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/tutorial...c8-6ed6a0040ad9/a-lowoverhead-syslog-mes.aspx