☰

文本中的日期值

tag-icon • tag-icon microsoft-excel microsoft-excel-2013
文本中的日期值

我有两个带日期的文本字段。我想将它们转换为 Excel 日期，然后从其中一个中减去另一个，例如：

cell 1: Wed 18 Feb 2015 18:00
Cell 2: Mon 16 Feb 2015 07:00

给 59 小时（59:00:00可以）

我相信这是正确的，但我得到的#value!结果是我猜测是因为 B5 没有正确的 Excel 日期时间值。

请指教

=DATEVALUE(MID(B5,5,2) & MID(B5,8,3) & RIGHT(B5,4)) + TIME(MID(B5,17,2),MID(B5,20,2))

答案1

您可以非常轻松地转换您的值，例如使用 D5 中的此公式

=MID(B5,5,20)+0

根据需要格式化，例如自定义格式，ddd d mmm yyyy hh:mm它们看起来和以前一样，但这次就 excel 而言，它们将是“真实”日期/时间，因此您只需从另一个中减去一个即可得到差值，即在 D5 和 E5 中使用转换值

=E5-D5

格式化结果单元格以[h]:mm:ss获取59:00:00

或者用此公式在一个单元格中进行转换和减法

=MID(C5,5,20)-MID(B5,5,20)

[h]:mm:ss与之前一样格式化结果单元格

参见后一种方法的示例这里

答案2

几项。我假设以日期形式列出的值在 Excel 中是字符串。如果您将它们作为日期，这会容易得多。

您的函数有两个问题DATEVALUE。首先，提取的值是错误的。这些是第一个字符串中的值：

MID(B5,5,2) = 18
MID(B5,8,3) = Feb
RIGHT(B5,4) = 8:00
MID(B5,17,2) = 18
MID(B5,20,2) = 00

其次，DATEVALUE需要解析字符串。因此您需要分隔符。例如：

=DATEVALUE(MID(B8,5,2) & "-" & MID(B8,8,3) & "-" & MID(B8,12,4))

注意：这种方法很脆弱，因为它假定了时间字符串的格式。如果任何值不一致，它就会中断。

获得正确的 Excel 日期后，您可以使用以下公式计算两者之间的时间跨度（以小时为单位）

=INT(B5-B6)*24 + INT(MOD(B5-B6,1)*24) &" hours"

这59 hours将为您的约会提供。

答案3

您的公式是正确的，但比这稍微复杂一些。对于这种情况，我通常会将其拆分为多个辅助单元格以降低复杂性，尽管您可以根据需要将其全部合并。假设您的日期/时间值在 A1 中：

Cell  Formula                                         Result
A1    Wed 18 Feb 2015                                 18:00
B1    =MID(A1,8,3)&" "&MID(A1,5,2)&", "&MID(A1,12,4)  Feb 18, 2015
C1    =RIGHT(A1,5)                                    18:00
D1    =TIMEVALUE(C1)                                  1/0/00 18:00
E1    =DATEVALUE(B1)+D1                               2/18/2015 18:00

然后对 E1 上的值进行日期/时间计算。

以下是该公式的组合、单元格版本：

=DATEVALUE(MID(A1,8,3)&" "&MID(A1,5,2)&", "&MID(A1,12,4))+(TIMEVALUE(RIGHT(A1,5)))

相关内容