How to Read the Correct Time/Duration Values from Google Spreadsheet

How to read the correct time/duration values from Google Spreadsheet

There are two new functions getDisplayValue() and getDisplayValues() that returns the datetime or anything exactly the way it looks to you on a Spreadsheet. Check out the documentation here

Handling time duration in Google Sheets Api

Your issue seems quite similar to the one in this post but at a larger scale...

You should convert row[4] value to minutes (or seconds if you need this accuracy) before adding that value to the total counter.

If the cells are formatted as duration (as you say it is) it should work without changes.(see code at the end of this post)

If not, ie if these values are returned as strings then a simple string manipulation will do the job for you like this :

example : testString = 12:34

function toMinutes(value){
  var minutes = Number(value.split(':')[0].replace(' ',''))*60+Number(value.split(':')[1].replace(' ',''));
  return minutes;
}

(code working as a function) will return 754 (60*12+34)

usage in your code : var horas = toMinutes(row[4]);

function toMinutes(value){
  var duration = new Date(value);
  var min = duration.getMinutes()+60*duration.getHours();
  return min;
}

You can eventually improve this function with a few error trapping features to handle cases where cell is empty of malformed... Since I don't know what the data look like I prefer let you do it yourself.

How to format a duration as HH:mm?

There is no need to use formulas for that, you can define your own custom formats.

Just go to Format -> Number -> More formats -> More date and time formats. It will open a window with several date and time formats to choose from. You can define your own as well, using the upper text area:

1. Click on the small arrow on the left, and select the first element you want to add: elapsed hours. Notice there are two different "hours", one for time and another for durations.
2. Type your separator text :
3. Click on the arrow again and add the second element: Elapsed Minutes.
4. Finally, click on each element you added, to determine the correct format (trailing zeros or not, for example).

Dynamically format duration in google sheets

01 Display Time
To get the duration displayed as needed.

s.mss
ss.mss
m:ss.mss
mm:ss.mss

You need a separate column D, paste this formula in D2, Take a look at This Sheet.

=ArrayFormula(IF(B2:B="",,REGEXEXTRACT(TEXT(B2:B,"h:mm:ss.000"),"[1-9].+")))

Explanation

1 - Format the number in this case Date/Time with TEXT function and set format to "h:mm:ss.000".

2 - REGEXEXTRACT the output with the regular_expression "[1-9].+" meaning check for the first number from the set [1-9] all number except 0 zero and +. everything behind it.

3 - ArrayFormula(IF(B2:B="",, to calculate only when the cells of the range B2:B is not empty.

02 Sum and subtraction

You can add and subtract normally, Google sheets see math on date and time as numbers, just use the appropriate format like this.

Go to Format > Numbers > Custom date and time.

Reading and writing time values from/to a spreadsheet using GAS

There does seem to be something strange going on with the behavior of your =dateToSeconds() versus your test() function. It looks like there is a bug that's causing the time discrepancy between the two. Please raise a bug for that in the issue tracker.

I was however able to get this working by using number formatting on the cells. Have a look at my modified copy of your spreadsheet. In cell A2 I entered "29/04/2012 11:00:00" and then set Format > Number > 15:59:00 Time. You can also do this programmatically by using Range.setNumberFormat("HH:mm:ss"); This results in cell A2 showing 11:00:00.

Then, I modified your test function to have this code:

function test() {
  var sheet = SpreadsheetApp.getActiveSheet();

  sheet.getRange("E2").setValue(dateToSeconds(sheet.getRange("A2").getValue()));
  Logger.log(sheet.getRange("A2").getValue());
  Logger.log(sheet.getRange("A2").getNumberFormat());

  var values = sheet.getRange("A4:C4").getValues();
  Logger.log(values[0][0] + " " + values[0][1] + " " + values[0][2]);
  sheet.getRange("E4").setValue(newTime2(values[0][0], values[0][1], values[0][2])).setNumberFormat("HH:mm:ss");
}

and instead of a newTime function, I created a newTime2 with this code:

function newTime2(hours, minutes, seconds) {
  var date = new Date();
  date.setHours(hours);
  date.setMinutes(minutes);
  date.setSeconds(seconds);
  Logger.log(date);
  return date;
}

I hope that helps.

How do I format a Google Sheets duration formatted cell to show days, hours and minutes in a human readable format?

The solution I found to be effective was writing a formula the converted the existing duration value into the individual duration component values of days, hours and minutes and then concatenates these values with strings that label the values appropriately.

The formula is as follows:

=int(B4)&" days "&hour(mod(B4,1))&" hours "&minute(mod(B4,1))&" minutes"

with B4 being the duration formatted cell containing the value

788:42:07

The cell containing the solution formula now displays the duration cell information as follows:

32 days 20 hours 42 minutes

---

I found the following resources useful in discovering the solution to this question.

https://infoinspired.com/google-docs/spreadsheet/convert-time-duration-to-day-hour-minute-in-google-sheets/

Google Sheets INT formula documentation

Google Sheets MOD formula documentation

Google Sheets HOUR formula documentation

Google Sheets TEXT formula documentation

Calculating time above 24 hours in Google Sheets

Select this box were my mouse is at, then time will add up over 24 hr. (it's in dutch)

---

Related Topics