Google Sheets format time incorrectly

Question

In my software for calling I have time in format 02:35 (duration of the call). I just highlight it and ctrl+c. Then I go to my google sheets and paste this text via ctrl+v into the corresponding column. The column formatting is hh:mm:ss. But after pasting I've got 02:35:00, instead of 00:02:35

I tried different types of formatting the column, but nothing has worked. I can't use formulas to make such formatting because a large team of people do the same procedures in their own sheets, so it should be automatically

This very likely has to do with how the data is inserted in the first place. Can you please tell us a bit more about how those durations are added to your sheet? And maybe share a copy of it (after removing possibly confidential information)? — Benoît Wéry
In my software for calling I have time in format 02:35 (duration of the call). I just highlight it and ctrl+c. Then I go to my google sheets and paste this text via ctrl+v into the corresponding column. The column formatting is hh:mm:ss. But after pasting I've got 02:35:00, instead of 00:02:35 — Vel Green
Possible duplicate of Format cell as minutes : seconds in google spreadsheets — Tedinoz
@Tedinoz cool way, but too difficult for a large team that makes such procedures in their own sheets — Vel Green
@VelGreen Your question suggests a single user (you) and no frequency of copy/paste. Please edit your question to clarify that this is "a large team" creating/editing their own sheets - you might also care to expand on what that actually means. — Tedinoz

player0 player0 · Accepted Answer · 2019-08-30T10:28:15

pre-format the column in which you paste stuff to Plain text:

if that's not enought then use this formula to fix your times:

=ARRAYFORMULA(IF(A:A<>"", TEXT("00:"&A:A, "mm:ss"), ))

Google Sheets format time incorrectly

1 Answers