# Help with time conversion formula

#### Harmonygirl

##### New Member
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 -->

### Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.
Are the values text values or formatted as time?

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?

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.

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.

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

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)

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?

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?

Replies
2
Views
88
Replies
2
Views
151
Replies
0
Views
253
Replies
5
Views
220
Replies
10
Views
175

Threads
1,206,761
Messages
6,074,780
Members
446,087
Latest member
PinkFloyd

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

### Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back