0

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

Vel Green
  • 67
  • 8
  • 1
    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 Aug 30 '19 at 09:52
  • 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 Aug 30 '19 at 10:01
  • 1
    Possible duplicate of [Format cell as minutes : seconds in google spreadsheets](https://stackoverflow.com/questions/38038093/format-cell-as-minutes-seconds-in-google-spreadsheets) – Tedinoz Aug 30 '19 at 10:23
  • @Tedinoz cool way, but too difficult for a large team that makes such procedures in their own sheets – Vel Green Aug 30 '19 at 10:25
  • 1
    @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 Aug 30 '19 at 10:32

1 Answers1

2

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

0

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

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

enter image description here

player0
  • 124,011
  • 12
  • 67
  • 124