Javascript Question posted in
A very good W3school tutorial can be found here.

Javascript – Convert number to time (mm:ss)

I would like to use a script to convert the column B2:B (text format) into minutes and seconds (mm:ss). I can do this with a formula (=B2/86400), but it should be implemented with a script:

COLUMN B    (COLUMN B)

244         (= 04:04)
211         (= 03:31)
229         (= 03:49)
246         (= 04:06)

how do I do that?

Tags:

2

Answers


  1. 0

    You can achieve this conversion using Google Apps Script in Google Sheets. Here’s a script that converts the numbers in column B to minutes and seconds format (mm:ss) and populates the adjacent cells in column C:

    function convertToTime() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  const range = sheet.getRange('B2:B' + sheet.getLastRow());
  const values = range.getValues().map(([seconds]) => {
    const minutes = Math.floor(seconds / 60);
    const paddedSeconds = seconds % 60 < 10 ? '0' : '';
    return `${minutes}:${paddedSeconds}${seconds % 60}`;
  });
  range.setValues(values);
}

    Make sure your column B contains only numbers representing seconds. If there are any non-numeric values, the script will throw an error.

    Login or Signup to reply.
  2. 0

    You could shorten the padded Seconds Section:

      const paddedSeconds = (seconds % 60).toString().padStart(2, "0");
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search