如何获得Microsoft Excel中两个日期之间的分钟差异？

问题描述

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

  AB 
 1日期：时间：
 2 12/11/12 2:36:25 
 3 12/12/12 1:46:14 
  

我想要做的是在两分钟的日期/时间戳中找到差异，以便我可以创建一个衰减曲线数据。所以在Excel中，我正在寻找这个（如果这个例子中的最小分数是错误的，我只是用手快速计算）：

  ABC 
 1日期：时间：时间已过（分钟）
 2 12/11/12 2:36:25  -  
 3 12/12/12 1:46:14 1436.82 
  

我已经看了一下，找到了几个方法来区别时间，但他们总是假设日期是一样的。我夸张了我的测量之间的时间，但是这几天之后，是什么让我感到悲伤。任何关于如何去做的建议或暗示将是巨大的。即使如果我可以用小数或十进制格式查找日期和时间之间的差异，那么我可以通过一个常数来获得我的答案。请注意，我有编程和Excel的经验，但请详细说明。时间和日期都被存储为数字，十进制值（实际浮点数）。

解决方案

日期是整数，时间是小数部分（1/24 = 1小时，1/24 * 1/60是一分钟等...）

日期时间差异计算如下：

  date2-date1 
 
 time2-time1 
  

这将在几天内给您答案，现在乘以24（小时），然后乘以60（小时） ）你在那里：

  time elapsed =（（date2-date1）+（time2-time1））* 24 * 60 
  

或

  C3 =（（A3-A2）+（B3-B2））* 24 * 60 
  

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:

   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):

   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.

解决方案

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...)

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:

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

or

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

这篇关于如何获得Microsoft Excel中两个日期之间的分钟差异？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！