两个日期在Excel中不起作用之间的小时数 [英] Number of Hours Between Two Dates Not Working in Excel

问题描述

根据要求，我提供了一个简化的屏幕快照，用于说明问题.

As requested, I have included a simplified screenshot that illustrates the issue.

如您所见，我减去了两个日期，并将其格式化为"h:mm:ss".为什么不给您两个日期之间经过的总小时数呢?有一个更好的方法吗?以下是一个很好的答案，但我试图弄清楚为什么无法按照此屏幕截图所示的方式进行操作.

As you can see, I subtracted the two dates and formatted it as "h:mm:ss". Why doesn't this give you the total amount of hours that have passed between the two dates? Is there a better way to do this? There is a great answer below, but I am trying to figure out why doing the way illustrated in this screenshot doesn't work.

我知道这里已经回答了类似的问题，但是无论出于何种原因，这对我来说都是行不通的.

I am aware that similar questions have been answered here, but for whatever reason this is not working for me at all.

我有两列:一是日期，一是时间.

I have two columns: one is a date, one is the time.

从当前突出显示的单元格中可以看到，时间"列实际上存储为包含时间的日期.在H列中，我将日期存储为序列号，以便十进制数字表示月，日，年，时，分和秒.当我从引用2/16/2016 1:20:01 PM的序列号中减去引用2/16/2016 3:20:01 PM的序列号并将该单元格格式化为"h:mm"时，我现在是2:00.为什么?????

As you can see from the currently highlighted cell, the "time" column is actually stored as date with the time included. In column H, I have the date stored as a serial code so that the decimal number refers to a month, day, year, hour, minute, and second. When I subtract the serial code that refers to 2/16/2016 3:20:01 PM from the serial code that refers to refers to 2/14/2016 1:20:01 PM and format that cell as "h:mm", I am getting 2:00. Why?????

一段时间以来，我一直在努力解决这个问题，这应该是愚蠢的，并且不起作用.非常感谢您的帮助，因此我可以继续进行更重要的事情.

I have been hacking away at this for a while and this is supposed to be stupid easy and it's not working. Any help is greatly appreciated so I can move on to more important things.

推荐答案

它确实有效.只需取两个日期之间的差，将包含该差的单元格的格式设置为Number，然后使用以下公式之一即可获取所需的单位.我会假设:

It does work. Just take the difference between the two dates, format the cell containing the difference as Number, and then use one of the following formulas to get the units you want. I will assume that:

A1的值为2/16/2016 3:20:01 PM

B1的值为2/14/2016 1:20:01 PM

进入C1输入A1 - B1，并将其格式化为Number

Into C1 enter A1 - B1, and format it as Number

如果您希望将天数作为天数，请保持C1不变.
如果需要几个小时，请使用=24*(A1 - B1)
如果需要分钟，请使用=24*60*(A1 - B1)
如果要秒，请使用=24*60*60*(A1 - B1)

If you want the difference as days, leave C1 as is.
If you want hours, use =24*(A1 - B1)
If you want minutes, use =24*60*(A1 - B1)
If you want seconds, use =24*60*60*(A1 - B1)

这篇关于两个日期在Excel中不起作用之间的小时数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！