2

I have a Google Spreadsheets in which I have:

 Date         | Start       | End         | Duration
 -------------+-------------+-------------+-------------------------
 11/24/2014   | 8:36 AM     | 10:12 AM    | ?
 ------------+--------------+-------------+-------------------------
 11/24/2014   | 8:06 AM     | 5:12 PM     | ?
 -------------+-------------+-------------+-------------------------     
              |             |             |

I want to calculate the duration and format it as [hr].[fraction of hour]. So, for the first row, the duration field would show 1.6. For the second row, the value would show 9.1.

Currently, I have the start and end columns formatted as times using h":"mm" "am/pm. I do not know how to do the conversion to [hr].[fraction of hour].

How do I do this in my Google spreadsheet?

Improve this question

2 Answers 2

Reset to default
0

I had a similar problem. I needed to create a timesheet. You need to use the Script Editor. You can open that using Tools -> Script editor... in your Google Sheet. Once there, add the following code to format a duration value in a Google sheet.

function FormatDuration(duration) {
  // Retrieve the hours and minutes
  var hrs = duration.getHours();
  var mins = duration.getMinutes();

  // Rounding the minutes to tenths of an hour
  var tenths = ((mins / 60).toFixed(1) * 10);
  if (tenths === 10) {
    tenths = 0;
    hrs = hrs + 1;
  }

  // Convert the result to a number to use in calculations
  var result = hrs + '.' + tenths;
  return Number(result);
}

I hope it helps.

Improve this answer
1
  • In this case a script isn't really necessary.
    – Jacob Jan Tuinstra
    Jan 13, 2015 at 22:23
6

A Google-Docs spreadsheet treats dates as 1 for every day past Dec 31, 1899. Today happens to be 41,973. Time is always a decimal portion of the day. Today at noon is 41,973.5. If you have a time value (a decimal) you can turn an number with 1 representing an hour by multiplying by 24 (number of hours in a day.

        enter image description here

The formula in D2 is (<end time> - <start time>) × <number of hours in a day> or =(C2-B2)*24. You can revert this result back to an actual time value by dividing by 24 (e.g. =D2/24 is 01:36).

Improve this answer
2
  • This is a cool trick, but it only works up to a point. I found a weird bug where if you try =round([duration], 1) and that duration is something like 0.2500 (ie some 15 minute interval you calculated) the result is not rounded correctly. This is due to the internal representation of the number in floating point, which you can see if you tack on enough zeros of precision in the formatting menu. As expected, rounding twice ( =round(round([duration],3),1) ) makes it work correctly. What a mess! Upvote for the cool trick though!
    – Daniel
    Sep 19, 2016 at 22:11
  • Time has always been a floating point nightmare due to its intrinsic nature of base-24 and base-60 numbering. Best to use actual time values whenever you can. The TIME function can pass a true 15 minute increment to the MROUND function; e.g. =mround(C2-B2, time(0, 15, 0))*24 CEILING and FLOOR can provide roundup and rounddown.
    – user82329
    Sep 19, 2016 at 22:37

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.