Help with time conversion formula

[Harmonygirl]

Hello,

I have an Excel problem that can hopefully be solved by a nifty formula!

I have 4 columns:

A B C D
0:00 1:17 56.15 0.06


Column A is in hours:minutes:seconds.milliseconds
Column B is in hours:minutes:seconds.milliseconds
Column C is in seconds.milliseconds
Column D is in hours:minutes:seconds.milliseconds

I would like to represent all these columsn in this format:
hours:minutes:seconds.milliseconds

Is there a simple formula I can use? I was looking at the CONVERT function, but it doesn't appear to work for milliseconds. <!-- / message --><!-- BEGIN TEMPLATE: ad_showthread_firstpost_sig --><!-- END TEMPLATE: ad_showthread_firstpost_sig -->

 

[Sal Paradise]

Are the values text values or formatted as time?

 

[Harmonygirl]

These were formatted as text (not done by me), and I am trying to automate summing up of values as well (thus the need for a consistent unit of measure and numeric values).

I think I might have a (complex, cumbersome) solution, but I'd love to see yours if you have the time?

 

[Sal Paradise]

Could you provide a bit more sample data? For instance:
0:00 1:17 56.15 0.06

Is the 0:00 0 hours and 0 minutes, or 0 minutes and 0 seconds? Ditto with the 1:17.

 

[Harmonygirl]

Sure, the first two columns are in minutes:seconds.

The last two columns are in seconds:milliseconds.

Thanks!

Could you provide a bit more sample data? For instance:
0:00 1:17 56.15 0.06

Is the 0:00 0 hours and 0 minutes, or 0 minutes and 0 seconds? Ditto with the 1:17.

 

[shemayisroel]

Hi,

Could you please post some sample data with expected results using excel jeanie

http://www.excel-jeanie-html.de/index.php?f=1

 

[Harmonygirl]

Sure, here it is - thanks for the link!

Some explanation as follows:

Column A is set to h:m:s.ms
Column B is set to h:m:s.ms
Column C is set to s.ms
Column D is set to s.ms

All cells are set to text.

What I want to do is have a consistent unit of time to represent all of these columns, and something that I can then take columns of this data and sum up the times.


<TABLE style="PADDING-RIGHT: 2pt; PADDING-LEFT: 2pt; FONT-SIZE: 11pt; FONT-FAMILY: Calibri,Arial; BACKGROUND-COLOR: #ffffff" cellSpacing=0 cellPadding=0 border=1><COLGROUP><COL style="FONT-WEIGHT: bold; WIDTH: 30px"><COL style="WIDTH: 179px"><COL style="WIDTH: 110px"><COL style="WIDTH: 110px"><COL style="WIDTH: 110px"><COL style="WIDTH: 110px"></COLGROUP><TBODY><TR style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center"><TD> </TD><TD>B</TD><TD>C</TD><TD>D</TD><TD>E</TD><TD>F</TD></TR><TR style="HEIGHT: 52px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">29</TD><TD style="FONT-WEIGHT: bold; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ccffcc">Step Name</TD><TD style="FONT-FAMILY: Arial; BACKGROUND-COLOR: #ccffcc">A</TD><TD style="FONT-FAMILY: Arial; BACKGROUND-COLOR: #ccffcc">B</TD><TD style="FONT-FAMILY: Arial; BACKGROUND-COLOR: #ccffcc">C</TD><TD style="FONT-FAMILY: Arial; BACKGROUND-COLOR: #ccffcc">D</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">30</TD><TD style="FONT-FAMILY: Arial; TEXT-ALIGN: left">Test</TD><TD style="FONT-FAMILY: Arial; TEXT-ALIGN: center">0:00</TD><TD style="FONT-FAMILY: Arial; TEXT-ALIGN: center">1:17</TD><TD style="FONT-FAMILY: Arial; TEXT-ALIGN: center">56.15</TD><TD style="FONT-FAMILY: Arial; TEXT-ALIGN: center">0.06</TD></TR></TBODY></TABLE>

Hi,

Could you please post some sample data with expected results using excel jeanie

http://www.excel-jeanie-html.de/index.php?f=1

 

[shemayisroel]

Harmonygirl,

I'll bump this one as I'm sure one of the time function guru's will provide a solution for you (i'm not a time function guru)

 

[Sal Paradise]

Are they formatted as time? Or are they formatted as text that looks like time? If they are all formatted as time, can't you just switch the format for all of them to h:m:s.ms?

 

[Harmonygirl]

The cells are formatted as Text.

Are they formatted as time? Or are they formatted as text that looks like time? If they are all formatted as time, can't you just switch the format for all of them to h:m:s.ms?