Home Forum Chat Forum Excel Time issue

Viewing 8 posts - 1 through 8 (of 8 total)
• Excel Time issue
• dan1980
Member

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.

soma_rich
Member

Hmmmm I saw that on a forum but it doesn’t work.

spawnofyorkshire
Subscriber

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

dannybgoode
Subscriber

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

soma_rich
Member

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?

dan1980
Member

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.

soma_rich
Member

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.

dan1980
Member

Which version of Excel are you using?

It seems to work fine with 2010.

http://imgur.com/a/HkDyV

The times to add are in “general” format, and everything else is in a custom [hh]:mm:ss.000 format.

Viewing 8 posts - 1 through 8 (of 8 total)

The topic ‘Excel Time issue’ is closed to new replies.