如何在Google Data Studio中将HH:MM格式文本转为数值型时长？

Got it, since you need to preserve the original HH:MM format in your Google Sheet for other calculations but need a numeric duration field in Data Studio, here are the most practical methods to do this directly in DS:

Method 1: Calculated Field (Recommended) #

This is the cleanest approach—no changes to your source sheet required:

1. Open your Data Studio report and navigate to the Data Source editing screen.
2. Locate your HH:MM text field, then click the Add a field button (top-right corner).
3. Paste this formula into the editor, replacing YourSleepField with your actual field name:

   (REGEXP_EXTRACT(YourSleepField, "^(\\d+):") * 3600) + (REGEXP_EXTRACT(YourSleepField, ":([\\d]+)$") * 60)
   

   • Breakdown: The first regex extracts the hour value, multiplies by 3600 to convert to seconds. The second regex extracts the minute value, multiplies by 60. Adding them gives total seconds.
4. Name your new field (e.g., Sleep Duration (Seconds)), set its Field Type to Number, and under Default Format, select Duration (Seconds).
5. Save the field—you can now use it as a numeric variable in charts, filters, or calculations in Data Studio.

Handling Edge Cases #

If your HH:MM data has extra spaces or inconsistent formatting (e.g., single-digit hours like 9:45 instead of 09:45), tweak the formula to include TRIM() and ensure the regex handles all valid cases:

(REGEXP_EXTRACT(TRIM(YourSleepField), "^(\\d+):") * 3600) + (REGEXP_EXTRACT(TRIM(YourSleepField), ":([\\d]+)$") * 60)

This will clean up any leading/trailing whitespace and still correctly extract hours/minutes regardless of single vs double digits.

Why This Works #

By doing the conversion directly in Data Studio, you keep your Google Sheet's original HH:MM format intact for other calculations, while getting the numeric duration field you need for DS visualizations.

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