如何在Google Sheets中通过Script将HH:MM时间转为十进制浮点数

If you need to turn those HH:MM time values (which Google Sheets stores as that quirky Sat Dec 30 1899 date object) into decimal hours—like converting 04:29 to ~4.48 or rounded to 4.5—here's a custom Apps Script function tailored for this task:

Step 1: Build the Custom Function #

1. Open your Google Sheet and head to Extensions > Apps Script to launch the script editor.
2. Delete the default myFunction() code and paste this instead:

function TIME_TO_DECIMAL(time, decimalPlaces = 2) {
  // Make sure the input is a valid time/date object
  if (!(time instanceof Date)) {
    return "⚠️ Invalid time value";
  }
  
  // Pull hours, minutes, and seconds from the stored date object
  const hours = time.getHours();
  const minutes = time.getMinutes();
  const seconds = time.getSeconds();
  
  // Calculate total decimal hours
  const totalDecimalHours = hours + (minutes / 60) + (seconds / 3600);
  
  // Round to your preferred decimal places (defaults to 2)
  return decimalPlaces !== undefined ? Number(totalDecimalHours.toFixed(decimalPlaces)) : totalDecimalHours;
}

3. Save the script (click the floppy disk icon) and name it something like "TimeConverter".

Step 2: Use the Function in Your Sheet #

Call this function directly in any cell to convert your time values:

• For 2 decimal places (e.g., 04:29 → 4.48):
  =TIME_TO_DECIMAL(A1) (replace A1 with your time cell)
• For 1 decimal place (matching your example of 04:29 → 4.5):
  =TIME_TO_DECIMAL(A1, 1)
• For the raw unrounded value:
  =TIME_TO_DECIMAL(A1, null)

How It Works #

Google Sheets stores time as a Date object with the date fixed to 1899-12-30 (a holdover from Excel). The function:

1. Checks that the input is a valid time/date to avoid errors.
2. Extracts the hours, minutes, and seconds from the object.
3. Converts minutes and seconds to fractions of an hour (since 60 minutes = 1 hour, 3600 seconds =1 hour) and adds them to the whole hours.
4. Rounds the result to your specified number of decimal places (default is 2 if you don't specify).

Bonus: No-Script Alternative #

If you prefer not to use a script, you can use a simple built-in formula:
=ROUND(A1*24, 1) (multiplying by 24 converts the day fraction to hours, and ROUND sets the decimal places)
But the custom script gives you extra control, like input validation and explicit rounding options.

内容的提问来源于stack exchange，提问作者TheOnlyAnil