Google Sheets Formulas – Finding the Longest Time Duration

formulasgooglegoogle sheetsgoogle-apps

I'm trying to find the longest time value (hh:mm:ss) and I'm looking for it in column I, starting at row 5. I've already tried using the LARGE function but that outputs a decimal, not a time.

=LARGE(I5:I, 1)

What's a function I could use that finds the largest time value in a column and outputs it?

Best Answer

Try this:

=max(I5:I)

This is a bit simpler than large(), and has the benefit that the result cell gets formatted automatically. But it actually gives the exact same result — your existing formula is also correct. To see the "decimal" result as a time, format the cell as Format > Number > Time.

How do date and time values work in spreadsheets?

Hope the following helps you understand what is going on with dates and times.

Google Sheets datetimes and durations are stored as numbers that correspond to the number of days since the start of "the Epoch", i.e., 30 December 1899. The fractional part corresponds to the fraction of the day. When a cell is formatted as a date, time or duration, the number is simply shown in years, months, days, hours, minutes and/or seconds, but its underlying value remains unchanged.

In other words, all date, datetime, time and duration values are just numbers formatted as such — the date 2020-04-17 is 43938, the datetime 2020-04-17 12:00 noon is 43938.5, the time 12:00 noon is 0.5, and the duration 06:00 is 0.25. The value 1 corresponds to one day, i.e., 24 hours.

Since the datetime value tells the number of days, you can increment a date by one day simply by adding 1, as in =A2 + 1. You can compare two dates the same way you would compare two numbers, as in =A2 < B2. You can add a duration to a date or a datetime to get a datetime, and subtract one datetime from another to get a duration.

One hour is 1/24 of a day, so to multiply an hourly rate such as $30/h by a duration, use =(24 * R2) * D2 where cell R2 contains the hourly rate and cell D2 contains the duration.

One minute is 1/24/60 of a day, so to multiply a revolutions per minute number such as 3000 rpm by a duration to get the total number of revolutions, use =(24 * 60 * R2) * D2.

One second is 1/24/60/60 of a day, so to multiply a speed given in meters per second such as 10 m/s by a duration to get a distance in meters, use =(24 * 60 * 60 * R2) * D2.

Note how these formulas do not convert the time value. It remains a dateserial value that expresses a fraction of the day. The value that is converted is the rate — it is multiplied to get rate per day from rate per hour, rate per minute or rate per second.

I find it easiest to always work with time and date using the built-in datetime number formats because that way you never have trouble with time values that exceed 24 hours, date values that exceed 365 days, and do not need to take leap years and such into account — all those details are automatically taken care of by the spreadsheet's built-in functionality if you just stick to formatting all date and time cells as dates and times, and use regular arithmetic to add and subtract them.

One often sees formulas that multiply a time value by 24 to get "digital hours" where 10.50 does not mean ten hours 50 minutes but ten and a half hours. This convention frequently causes misunderstandings, and also makes it harder, not easier, to use the value in further processing. For example, a "digital hour" value cannot be directly added to a date to get a datetime.

That is why I prefer using the built-in time formats that express the same as 10:30. This time value can be directly added to a date to get a datetime.

Related Topic