Adding Calculated Times and Formatting

Hello everyone. I'm having a bit of trouble using the custom format function in Excel 2016. In a nutshell what I want is to be able to enter a time in the format hh:mm:ss:000, and add to that another time in the same format, and get the result. For example, I would like Excel to perform the following calculation:

12:34:56.789 + 1.234 = 12:34:58.023

I created a spreadsheet (attached) in an attempt to accomplish this but have thus far failed miserbaly. I have searched for "custom formatting time" but haven't been able to find the solution.

In my spreadsheet, I'd like to take the time entered into cell B6, add to it the seconds displayed in cells C10, C11, C12..., and display the results in D10, D11, D12...

So in my attached spreadsheet the result displayed in cell D10 should be, 00:10:59.457, but as you can see it is not.

I hope that's enough information. Thanks in advance for any help you can provide.

Originally Posted by FDibbins

to begin with, looks like you are referencing the wrong cell in the calc? C$6+C10 instead of B$6+C10

Good catch. That was a stupid typo. Thanks.

Originally Posted by FDibbins

So adding 1.5 to any time is actually adding 36 hours (1.5 days) to that time.

This was the piece of info I was missing. I converted 1.5 seconds to days like this 1.5/(60*60*24). That solved my issues.

Thanks for the speedy solution. Have a nice day!

Originally Posted by MrShorty

Ford is correct. If I understand, I made three edits to your spreadsheet that I think will make it do what you want:

1) Enter 0:0:5 into B5 so that Excel will recognize this as 5 seconds and properly convert to the appropriate serial number. As Ford explained, this number is just what fraction of a day is 5 seconds, so I could have also entered =5/24/60/60
2) Then I corrected the formula D10 to reference B$6 as Ford pointed out.
3) Then I formatted all of the time cells to elapsed seconds [ss].000. You could also use the elapsed hours ([h]:mm:ss) format that Ford suggested, or even elapsed minutes [mm]:ss.000

Thanks Mr. Shorty. Armed with the new understanding that Excel represents time in days, and the changes you made, my spreadsheet is working the way I intended.

Originally Posted by FDibbins

oops you said 1.5 seconds, not 15 seconds...
0:00:1.5

I knew what you meant. Thanks for the clarification though.