[Solved] How can I set formatting for just a time duration in a Google Sheet using Google Apps Script?

Question

If the original value is in seconds as in 5.323 for five seconds and 323 milliseconds, you can convert it to a dateserial value by dividing it like this:

dataserial = seconds / 24 / 60 / 60

Append the resulting value to the spreadsheet and format the cell or the whole column as duration, say [h]:mm:ss.SSS where [h] signifies elapsed hours.

The hours field needs to be expressed as elapsed hours to correctly handle durations that exceed 24 hours. Minutes and seconds never exceed 60 and can be handled the same way regardless of whether they express calendar time or elapsed time.

See this answer for an explanation of how date and time values work in spreadsheets.

Accepted Answer

If the original value is in seconds as in 5.323 for five seconds and 323 milliseconds, you can convert it to a dateserial value by dividing it like this:

dataserial = seconds / 24 / 60 / 60

Append the resulting value to the spreadsheet and format the cell or the whole column as duration, say [h]:mm:ss.SSS where [h] signifies elapsed hours.

The hours field needs to be expressed as elapsed hours to correctly handle durations that exceed 24 hours. Minutes and seconds never exceed 60 and can be handled the same way regardless of whether they express calendar time or elapsed time.

See this answer for an explanation of how date and time values work in spreadsheets.