在Excel中减去日期时间 [英] subtract datetime in excel

问题描述

我正在尝试在excel的两个单独的列中减去两个日期，以查找时差.以h:mm格式.源格式如下.

I'm trying to subtract two dates in two separate columns in excel, to find time difference. in h:mm format. The source format is the following.

2014/11/18上午9:42:39-2014/10/8下午12:17:13

11/18/2014 9:42:39 AM - 10/8/2014 12:17:13 PM

我已将列更新为具有正确格式的日期.

I have updated the columns to a date with the correct format.

当我从彼此减去两列时，我得到以下hh:mm值.

When I subtract the two columns from eachother i get the following hh:mm value.

21:25

在我看来，该计算未考虑日期.

It seems to me that the calculation is not taking the dates into account.

我该怎么做才能纠正这个问题?

What can i do to correct this?

推荐答案

好吧，问题在于您需要将日期转换为数字格式，然后进行计算.

Ok, the issue is that you need to convert the dates to number format, and then due the calculations.

步骤1) 将两个日期都转换为数字，并保留小数点后四位

Step 1) Convert both dates to numbers, with 4 decimal places

第2步) 减去数字，得出日期之间的时差.小数点前的数字是整天，小数点后的数字是小时，分钟，秒等.

Step 2) Subtract the numbers, to give the time difference between the dates. The numbers before the decimal place are full days, and the numbers after the decimal place are hours, minutes, seconds, etc.

第3步) 使用=Mod(XX11,1)*24将小时，分钟，秒与天分开，并计算小时数.根据需要重复分钟和秒，使用60而不是24.

Step 3) Use =Mod(XX11,1)*24 to separate the hours, minutes, seconds from the days, and calculate the number of hours. Repeat for minutes, and seconds, as needed, using 60 instead of 24.

您现在将拥有不同的天数，小时数，分钟数，等等.根据需要将它们放到公式中.

You will now have separate amounts of days, hours, minutes, etc. Put these together in a formula as necessary.

这篇关于在Excel中减去日期时间的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！