Google-sheets – Time duration formatting in Google Spreadsheets

formulasgoogle sheetsgoogle-sheets-dates

A couple issues here:

  1. I have a formula that is trying to determine if a time duration (ex 7:51) is greater than or less than a baseline duration value. When typing the formula I have to put the baseline number in quotations

    ex =IF(B3="SB",IF(AND(I3<"13:01",J3>49,K3>49,L3>5,M3<"12:01"),"P","F")

    otherwise the formula thinks I'm referring to a range of cells. Doing it this way doesn't produce an error but the formula doesn't work correctly in that no matter if the time entered is less than or greater than the baseline, the same result is produced.

  2. I can make the formula work by inputting the data without a colon and separating the min:sec with a period instead, but I'm just not happy doing things this way. I want to be able to enter the data using the true format which is "min:sec".

I feel like the answer here is within the way the cell is formatted to display the data, but I've tried many many options and none of them seem to do the trick. I have zero experience with script editing either so that's another avenue that has not been explored, nor do I even know what capabilities that could unlock.

Best Answer

On Google Sheets, a duration of 13 minutes and 1 second is shown in the formula bar as follow

00:13:01.000

To display the above value as 13:01, apply a custom format by clicking on Format > More formats... then apply the following mm":"ss

By the other side, Google Sheets formulas doesn't allow to write date, time and duration values directly. The alternatives are

  • Write the date/time/duration in a cell and use a reference to that cell in the formula
IF(AND(I3<A1,J3>49,K3>49,L3>5,M3<A2),"P","F")
  • Use a function that returns the corresponding date, time or duration. The following example use TIMEVALUE
IF(AND(I3<TIMEVALUE("0:13:01"),J3>49,K3>49,L3>5,M3<TIMEVALUE("0:12:01")),"P","F")
  • Write the serialized number corresponding to the date, time or duration. In the following example 13 minutes and 1 second is 0.009039351852 and 12 minutes and 1 second is 0.008344907407
IF(AND(I3<0.009039351852,J3>49,K3>49,L3>5,M3<0.008344907407),"P","F")
Related Topic