如何在Microsoft Excel中得到两次约会之间的分钟差?

I am doing some work in Excel and am running into a bit of a problem. The instruments I am working with save the date and the time of the measurements and I can read this data into Excel with the following format:

我正在用Excel做一些工作，遇到了一些问题。我正在使用的仪器保存了测量的日期和时间，我可以用以下格式将数据读入Excel:

   A           B
1 Date:      Time:
2 12/11/12   2:36:25
3 12/12/12   1:46:14

What I am looking to do is find the difference in the two date/time stamps in mins so that I can create a decay curve from the data. So In Excel, I am looking to Make this (if the number of mins in this example is wrong I just calculated it by hand quickly):

我要做的是找出两个日期/时间戳在分钟内的差异，这样我就可以从数据中创建一个衰减曲线。所以在Excel中，我想做这个(如果这个例子中的分钟数错了，我只是用手工快速计算):

   A           B                 C
1 Date:      Time:       Time Elapsed (Minutes)
2 12/11/12   2:36:25               -
3 12/12/12   1:46:14            1436.82

I Have looked around for a bit and found several methods for the difference in time but they always assume that the dates are the same. I exaggerated the time between my measurements some but that roll over of days is what is causing me grief. Any suggestions or hints as to how to go about this would be great. Even If I could find the difference between the date and times in hrs or days in a decimal format, I could just multiple by a constant to get my answer. Please note, I do have experience with programming and Excel but please explain in details. I sometimes get lost in steps.

我环顾四周，发现了几种不同的方法，但他们总是认为日期是一样的。我夸大了测量之间的时间，但这几天的滚动是导致我悲伤的原因。任何关于如何去做这件事的建议或提示都是很好的。即使我能用十进制的格式计算出日期和时间的差值，我也可以用一个常数乘以它来得到我的答案。请注意，我确实有编程和Excel的经验，但请详细说明。我有时会迷失方向。

3 个解决方案

#1

time and date are both stored as numerical, decimal values (floating point actually). Dates are the whole numbers and time is the decimal part (1/24 = 1 hour, 1/24*1/60 is one minute etc...)

时间和日期都存储为数字、十进制值(实际上是浮点数)。日期是整数，时间是小数部分(1/24 = 1小时，1/24*1/60 = 1分钟…)

Date-time difference is calculated as:

日期-时间差的计算方法为:

date2-date1

time2-time1

which will give you the answer in days, now multiply by 24 (hours in day) and then by 60 (minutes in hour) and you are there:

它会在几天内给出答案，现在乘以24(一天24小时)然后乘以60(一小时60分钟)你就到了:

time elapsed = ((date2-date1) + (time2-time1)) * 24 * 60

or

或

C3 = ((A3-A2)+(B3-B2))*24*60

#2

To add a bit more perspective, Excel stores date and times as serial numbers.

要添加更多的透视图，Excel将日期和时间存储为序列号。

Here is a Reference material to read up.

这是一份参考资料。

I would suggest you to use the following:

我建议你使用以下方法:

Combine date to it's time and then do the difference. So it will not cause you any issues of next day or anything.

把日期和时间结合起来，然后做不同的事情。所以它不会给你带来任何明天的问题。

Please refer to the image with calculations. You may leave your total minutes cell as general or number format.

请参照图片进行计算。您可以保留您的总分钟单元格作为一般或数字格式。

• MS EXCEL Article: Calculate the difference between two times

  MS EXCEL文章:计算两次的差值

• Example as per this article

  根据本文的例子。

#3

I think =TEXT(<cellA> - <cellB>; "[h]:mm:ss") is a more concise answer. This way, you can have your column as a datetime.

I think =TEXT( - ;“[h]:mm:ss”)是一个更简洁的回答。这样，您可以将列作为一个datetime。