Share via

Calculating time average in milliseconds on Excel with 00:00:00:01 format

Anonymous
2024-08-07T09:32:19+00:00

Hi,

I'm trying to calculate the average duration length in seconds and milliseconds. The data I have is in 00:00:00:01 format (as opposed to 00:00:00.01 with a full-stop before the millisecond value). Excel doesn't seem to work with the format that I have.

Example of the dataset:

00:00:00:05
00:00:00:11
00:00:00:10
00:00:00:05
00:00:00:06
00:00:00:31
00:00:00:09
00:00:00:09
00:00:00:10
00:00:00:10
00:00:01:00
00:00:00:07

Any help would be appreciated!

Best regards,

Deborah

Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

4 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2024-08-10T16:09:28+00:00

    A formula perhaps?:

    =TIMEVALUE(SUBSTITUTE(A2,":",".",3))

    1 person found this answer helpful.
    0 comments
  2. Andreas Killer 144.1K Reputation points Volunteer Moderator
    2024-08-07T10:12:40+00:00

    Deborah,

    Write the format you have into a text file, then import it using Power Query with this query:

    let

        Source = Csv.Document(File.Contents("C:\Users\Killer\Downloads\test.txt"),[Delimiter=",", Columns=1, Encoding=1252, QuoteStyle=QuoteStyle.None]),

        #"Split Column by Delimiter" = Table.SplitColumn(Source, "Column1", Splitter.SplitTextByEachDelimiter({":"}, QuoteStyle.Csv, true), {"Column1.1", "Column1.2"}),

        #"Merged Columns" = Table.CombineColumns(#"Split Column by Delimiter",{"Column1.1", "Column1.2"},Combiner.CombineTextByDelimiter(".", QuoteStyle.None),"Times"),

        #"Changed Type" = Table.TransformColumnTypes(#"Merged Columns",{{"Times", type duration}})

    in

        #"Changed Type"

    Load into Excel as table, , apply the format hh:mm:ss.00 and enable the Total row with average. Result:

    Andreas.

    1 person found this answer helpful.
    0 comments
  3. Anonymous
    2024-08-11T10:14:15+00:00

    That does work better, thanks alot!

    0 comments
  4. Anonymous
    2024-08-10T12:37:27+00:00

    Hi Andreas,

    thank you very much! It seems to work now.

    Best,

    Deborah

    0 comments