Dec 7, 2024
time duration format
Details
Informational notification.
This question is locked and replying has been disabled.
Community content may not be verified or up-to-date. Learn more.
Hi Alireza,
It sounds like you are using the
text() function to format those values. That would produce text strings.To get numeric duration values, remove the
text() function and use Format > Number > Duration or Format > Number > Custom number format instead.If you need more help, please share a sample spreadsheet with realistic-looking sample data, and use the spreadsheet to show what you mean.
Cheers --Hyde
Original Poster Alireza Madadi marked this as an answer
All Replies
Hi Alireza,
It sounds like you are using the
text() function to format those values. That would produce text strings.To get numeric duration values, remove the
text() function and use Format > Number > Duration or Format > Number > Custom number format instead.If you need more help, please share a sample spreadsheet with realistic-looking sample data, and use the spreadsheet to show what you mean.
Cheers --Hyde
Original Poster Alireza Madadi marked this as an answer
Dec 7, 2024
Hi Alireza,
Thank you for sharing a sample spreadsheet. The spreadsheet is in the Finnish locale and thus uses this format to represent the time of day:
klo 19.22.46. That is 22 minutes and 46 seconds past 7pm.I could not find a duration format that would be easy to enter in the Finnish locale. The default duration format seems to insist on the "klo" prefix, and while the "internal" format appears to be like
0:22:46.200 for 22 minutes, 46 seconds and 200 milliseconds, Google Sheets does not appear to accept that format for data input in that locale.You have been using the format
0:09.37 for entering durations. I think you that would mean 0 minutes, 9 seconds and 37 centiseconds. Unfortunately, that is format is not recognized by Google Sheets because of the Finnish locale setting, so it is interpreted as a text string.I do not know a turnkey way to make Google Sheets understand your preferred duration format, and as a workaround, would recommend that you set File > Settings > Locale to United States. You should then be able to enter durations in the format
0:0:9.370. To display that as 0:09.37, choose Format > Number > Custom number format > [m]:ss.00.One easy way to tell whether a value is recognized as a numeric duration or misinterpreted as a text string is to look at cell alignment. Numbers align to the right by default, and text strings align to the left.
See how date and time values work in spreadsheets for a detailed explanation of what is going on.
Cheers --Hyde