Excel 2005：两天之间的差异，计算小时差异
我一直在尝试让这个 Excel 函数正常工作，但遇到了困难。我正在尝试计算两个日期之间的确切天数差异，同时考虑开始时间以及开始日。看来这应该是一个普遍的需求？
示例：
Start Date End Date Expected Result
9/20/2010 8:00am 9/22/2010 3:00pm 2.3 days
我猜测是 .3，但您明白了:) 我当前的公式如下所示：
=IF(End < NOW(), 0, IF(Start >= NOW(), End-Start+1, End-NOW()))
它几乎完美地工作......唯一的问题是，如果今天在开始/结束日期之间，它只计算全天，不包括部分日期。
以下是此函数的参数：
- 今天在开始日期之前： 计算完整天数
- 今天在开始日期和结束日期之间： 计算剩余的确切天数（以开始/结束日期为准）考虑到小时数
- 今天是开始日期：计算剩余的确切天数，考虑开始/结束时间
- 今天是开始日期，剩余的小时数：计算剩余的确切天数，考虑开始/结束时间考虑结束时间
- 今天是结束日期，结束时间之后：零
- 今天是结束日期，剩余时间：计算剩余的确切天数，考虑开始/结束时间
- >今天是结束日期之后：零
I've been trying to get this Excel function working correctly, and I've hit a wall. I'm trying to calculate the exact difference in days between two dates, taking the start time into account as well as the start day. Seems like this should be a common need?
Example:
Start Date End Date Expected Result
9/20/2010 8:00am 9/22/2010 3:00pm 2.3 days
I'm guessing at the .3, but you get the idea :)
My current formula looks like this:
=IF(End < NOW(), 0, IF(Start >= NOW(), End-Start+1, End-NOW()))
It works almost perfectly...the only hiccup is if today is between the start/end dates, it only calculates full days, and does not include partials.
Here are the parameters for this function:
- Today is before start date: Calculate full number of days
- Today is between start and end date: Calculate exact days left, taking start/end hours into account
- Today is start date: Calculate exact days left, taking start/end hours into account
- Today is start date, hours left: Calculate exact days left, taking start/end hours into account
- Today is end date, after end time: zero
- Today is end day, hours left: Calculate exact days left, taking start/end hours into account
- Today is after end date: zero
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
我刚刚在 Excel 2000 中尝试过此操作，并且问题 [End - Now())] 的子句在答案中确实包含小数位。您是否已将列格式设置为在显示中包含小数位？
但是，我会将 [End-Start+1] 更改为 [CEILING(End-Start,1)]。这会将值四舍五入到最接近的整数天数。这给出：
I've just tried this in Excel 2000 and the clause with the problem [End - Now())] does include decimal places in the answer. Have you set the column format to include decimal places in the display?
However, I would change [End-Start+1] to [CEILING(End-Start,1)]. This rounds up the value to the nearest whole number of days. This gives: