Search code examples
google-sheetsgoogle-apps-scriptgoogle-sheets-formula

Formula defaults to hh:mm despite number format HH:mm

hh:mm

I have a sheet. I want the header to show the mean value of each column. SUBTOTAL(101, range) ignores hidden rows, plus I want to display the mean end time in the same 24h time format as the start time. I just don't see why the same formula yields a different format in a different column.

I expected that using HH instead of hh would yield a 24-hour time format, but no. HH:mm

Here is an example of my damage: My Dumb Sheet

Date format: same result.

Expected: HH:mm for 24-hour times.
Observed: 14:00 shows as 2:00.

Solution

  • The underlying times for column E includes a date for some rows, which when averaged creates a new date time, whose time corresponds to 02:14 in 24h format:

    Col E + Subtotal Underlying data number Underlying data full date
    02:14 32,397.09 11/09/1988 02:14:18
    15:59 0.67 30/12/1899 15:59:49
    19:12 45,353.80 02/03/2024 19:12:20
    14:45 45,354.62 03/03/2024 14:45:51
    13:45 45,355.57 04/03/2024 13:45:49
    15:03 45,356.63 05/03/2024 15:03:25
    16:18 45,357.68 06/03/2024 16:18:52
    16:34 0.69 30/12/1899 16:34:00