How Do I Get The Number Of Days For A Duration Where The Number Of Hours Exceeds 24 In Google Sheets?

I am using google sheets where there is a duration value of 69:41:00 where it's 69 hours, 41 minutes, 0 secs. There doesn't seem to be a function to convert this to days, hours and

Solution 1:

I was going to add, why don't you just use a custom format of

ʺd\d hh\h mm\mʺ ?

This works fine in Excel but not in GS because it uses a different base for dates so duration like 69:41:00 would be interpreted as 1/1/1900 21:41 and the days are not correct. So you would have to break it down into days (whole numbers) and hours+minutes (fractions of a day) like this

=text(int(A1),ʺ#0\d ʺ)&text(mod(A1,1),ʺHH\h MM\mʺ)

You can make it work in Google Scripts if you want to by adjusting the date - should work OK for durations up to 1 month.

The reason for adding 2 to the date is that a time like 03:21:00 (less than a day) is seen as a date - namely 30th December 1899 ! So I add 2 to it to make it 1st January 1900. However, now the day part of the date is 1 and I want it to be zero. So I have to subtract 1 from the day further down.

This strange behaviour is probably why you're advised to do it the other way and work in milliseconds, but I was just interested to see if there was a way of making the original code work.

/**
 * Format Duration to Days,Hours,Minutes
 *
 * @param {duration} input value.
 * @return Days,Hours,Minutes.
 * @customfunction
 */functionFormatDuration(duration) {
  // Add 2 days to the datevar date=newDate(duration.setDate(duration.getDate()+2));
  Logger.log(date.getDate());
  var hours = duration.getHours();  
  // Take 1 off the day part of the date  var days = date.getDate()-1;
  var mins = duration.getMinutes();

  // Convert the result to a number to use in calculationsvar result = days + 'd ' + hours + ' h '+ mins+' min';
  return result;
}

Solution 2:

function(durations){
    var timeArr = durations.split(':'); //["69","41","00"]
    //your code
}

getHours is a method of object Date.

var t = newDate;
t.getHours();

Solution 3:

How do you expect to get more than 24hours from a Date object? It is not the same as what you expect as Duration. Date is for points of time in calendar, so at most you'd get the 23:59:59 of any day. You can get date2 - date1 = milliseconds diff, and work on it, as following;

functionFormatDuration(date1, date2) {
  var milliseconds = date2 - date1;
  var mins = Math.floor((milliseconds / (1000*60)) % 60);
  var hours = Math.floor((milliseconds / (1000*60*60)) % 24);
  var days = Math.floor(milliseconds / (1000*60*60*24));

  var result = days + ' d ' + hours + ' h '+ mins + ' min';
  console.log(result);
}

FormatDuration(newDate(2000, 5, 1, 5, 13, 0, 0), 
           newDate(2000, 5, 2, 15, 31, 0, 0))

You can find more details here