Excel Time issue

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.

    Premier Icon 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

    Premier Icon 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.