Subscribe now and choose from over 30 free gifts worth up to £49 - Plus get £25 to spend in our shop
Trying to add seconds to a time, I have 1 column with a long list of times:
0.05
0.075
0.1
0.125
0.15
0.175
0.2
1.225
0.25
0.275
0.3
0.325
I want to add these to a start time of 10:07:01, i use the formula =($A$3+TIME(0,0,B4))but it will only add whole seconds not the milliseconds. Any ideas?
The standard "time" cell format doesn't support milliseconds I don't think, so the maths won't work.
Try a custom format "[h]:mm:ss.000" without the ""s and you should be good to go.
Hmmmm I saw that on a forum but it doesn't work.
try
=sum(time(hour($A$4),minute($A$4),second($A$4))+TIME(0,0,B4))
Edit:
sorry misread I the post above :/ my suggestion is of no help here and can't find a solution to help you
Have you set the number format to the number of decimal places you are working with. If not Excel sometimes rounds up or down accordingly.
It is adding the numbers correctly in the background but only displays whole numbers sometimes unless told to do otherwise...
Cheers
Danny B
The problem appears to be that your data isn't in the right format for Excel to work in.
Assume your time in milliseconds is in column A: In B, create the formula =--TEXT(A1,"00\:00\:00.000") Now your times are are converted from your OP into excel time formats for milliseconds.
Now, format your cells apart from coloumn A to the [hh]:mm:ss.000 format, and then the formula to add the times =[time cell]+B1 There is no need to use the time function.
Hope this makes sense.
Dan That makes sense but, it doesnt work, If you put the values I have pasted above in a sheet and apply your formula I just get a #value error.
Which version of Excel are you using?
It seems to work fine with 2010.
[url= http://imgur.com/a/HkDyV ]http://imgur.com/a/HkDyV[/url]
The times to add are in "general" format, and everything else is in a custom [hh]:mm:ss.000 format.