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

How to Process Rows of Google Sheet Singly?

Updated April 05, 2018 19:03 PM

auto timestamps for multiple column separately

Updated March 25, 2018 11:03 AM

Detect whether a cell has strikethrough format

Updated April 18, 2018 20:03 PM