Convert hh:mm to mm:ss on entry

by etudes   Last Updated January 12, 2018 21:03 PM

I would like to input time durations of minutes and seconds quickly (without needing to include an hours value). I have come to the conclusion the most convenient way to do this is to have a script that can divide a cell by 60 upon entry.

An example:

  1. Custom format some cells as "mm:ss"
  2. Enter "4:23" into a cell, intending 4 minutes and 23 seconds but which would be processed by Google Sheets as 4 hours and 23 minutes.
  3. Upon pressing "enter", the script automatically divides the entry by 60, converting h:mm to mm:ss.

In my case, I am trying to get this to happen for all cells in columns K and L (excluding cells that I enter non-numbered text into)

How would I do this using Google Sheets?

Related Questions

Avoiding an authorization request for google scripts

Updated November 23, 2018 16:03 PM

Which is better: onEdit or onChange trigger?

Updated August 14, 2018 16:03 PM