如何获得Google小时数超过24小时的天数？ [英] How do I get the number of days for a duration where the number of hours exceeds 24 in google sheets?
问题描述
我使用的Google表格的时长值为69:41:00，时长为69小时41分钟0秒。似乎没有将此转换为日，小时和分钟的功能，因此我进行了一些搜索，并且有人提出了自定义函数。不确定它是如何工作的，但是从原来的做了一些改变，以适应我所需要的。下面的代码：
结果应该是2d 21h 44 min相反，我得到0d 21小时35分钟。我在这里做错了什么？
我要添加，为什么不使用自定义格式
d\\d hh\h mm\m\"？
这适用于Excel，但不适用于GS，因为它对日期使用不同的基数，所以持续时间如69 ：41：00会被解释为1/1/1900 21:41而且日子不正确。所以你不得不把它分解成几天（整数）和几小时+分钟（一小部分），像这样
= text（int（A1），＃0 \ d）& text（mod（A1,1），HH \h MM\m）
如果您想通过调整日期，您可以在Google脚本中使用它 - 应该可以正常工作，持续时间最长为1个月。
将日期加2的原因是，像03:21:00（少于一天）的时间被视为日期 - 即1899年12月30日！所以我加了2来制作1900年1月1日。然而，现在日期的一部分是1，我希望它是零。所以我必须从下一天减去1。
这种奇怪的行为可能是为什么你建议以另一种方式做，并以毫秒为单位工作，但我只是有兴趣看看是否有办法使原始代码工作。
/ **
*格式持续时间到天，小时，分钟
*
* @param {duration}输入值。
* @return天，小时，分钟。
* @customfunction
* /
函数FormatDuration（持续时间）{
//向日期添加2天
var date = new Date（duration.setDate（duration .getDate（）+ 2））;
Logger.log（date.getDate（））;
var hours = duration.getHours（）;
//将日期的一部分取掉1
var days = date.getDate（） - 1;
var mins = duration.getMinutes（）;
//将结果转换为计算中使用的数字
var result = days +'d'+ hours +'h'+ mins +'min';
返回结果;
}
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 minutes so I did some searching and some had suggested a custom function. Not sure exactly how it works but made some changes from the original to fit what I needed. The code below:
/**
* Format Duration to Days,Hours,Minutes
*
* @param {duration} input value.
* @return Days,Hours,Minutes.
* @customfunction
*/
function FormatDuration(duration) {
// Retrieve the hours and minutes
var hrs = duration.getHours();
var days = Math.floor(hrs/24);
var hours = hrs % 24;
var mins = duration.getMinutes();
// Convert the result to a number to use in calculations
var result = days + 'd ' + hours + ' h '+ mins+' min';
return result;
}
The result should be 2d 21h 44 min but instead I got 0d 21 h 35 min. Am I doing something wrong here?
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
*/
function FormatDuration(duration) {
// Add 2 days to the date
var date=new Date(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 calculations
var result = days + 'd ' + hours + ' h '+ mins+' min';
return result;
}
这篇关于如何获得Google小时数超过24小时的天数？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
