在Excel中将文本日期/时间转换为实际日期时间

Question

在Excel中将文本日期/时间转换为实际日期时间

exceldatetimedata-conversion

23

我的数据来自一个应用程序，其中有一个文本看起来像Excel表格中的日期/时间。如何将“3/24/2016 11:22:07 PM”（作为文本）转换为实际的日期/时间？我已经尝试过格式化单元格，但不起作用。

- user3085606

4个回答

2

=DATEVALUE(A1)+TIMEVALUE(A1) 似乎也可以工作，因为每个函数都只返回其在字符串中识别到的值。也就是说，DATEVALUE() 忽略时间部分，而 TIMEVALUE() 忽略日期部分。

- Edward Johnson

2

1）尝试使用DATEVALUE函数，看看是否适用于您的情况。

2）更可靠的方法是手动剥离文本并将其插入Excel日期值中，因为DATEVALUE并不总是有效。您需要使用以下函数的组合：

DATE
TIME
IF
FIND
MID
LEFT
RIGHT
LEN

在我看来，最简单的方法是使用多个辅助列来构建所有步骤。每个步骤一个列。当您得到最终答案时，可以将辅助列中的公式替换或复制粘贴到最终公式中，直到只剩下一个变量。我之所以这样说，是因为最终公式仅涉及1个变量，会变得非常冗长/丑陋，并且如果出现拼写错误、忘记括号或其他问题，很难进行故障排除。当我采用这种方法时，我一共使用了14列（包括最终公式）。当我将所有内容都整合到一个公式中时，结果如下：

DATE(RIGHT(LEFT(A3,FIND(" ",A3)-1),4),LEFT(LEFT(A3,FIND(" ",A3)-1),FIND("/",LEFT(A3,FIND(" ",A3)-1))-1),MID(LEFT(A3,FIND(" ",A3)-1),FIND("/",LEFT(A3,FIND(" ",A3)-1))+1,FIND("/",LEFT(A3,FIND(" ",A3)-1),FIND("/",LEFT(A3,FIND(" ",A3)-1))+1)-FIND("/",LEFT(A3,FIND(" ",A3)-1))-1))+TIME(LEFT(RIGHT(A3,LEN(A3)-FIND(" ",A3)),FIND(":",RIGHT(A3,LEN(A3)-FIND(" ",A3)))-1)+IF(AND(LEFT(RIGHT(A3,LEN(A3)-FIND(" ",A3)),FIND(":",RIGHT(A3,LEN(A3)-FIND(" ",A3)))-1)<12,RIGHT(RIGHT(A3,LEN(A3)-FIND(" ",A3)),2)="AM"),0,12),MID(RIGHT(A3,LEN(A3)-FIND(" ",A3)),FIND(":",RIGHT(A3,LEN(A3)-FIND(" ",A3)))+1,FIND(":",RIGHT(A3,LEN(A3)-FIND(" ",A3)),FIND(":",RIGHT(A3,LEN(A3)-FIND(" ",A3)))+1)-FIND(":",RIGHT(A3,LEN(A3)-FIND(" ",A3)))-1),MID(RIGHT(A3,LEN(A3)-FIND(" ",A3)),FIND(":",RIGHT(A3,LEN(A3)-FIND(" ",A3)),FIND(":",RIGHT(A3,LEN(A3)-FIND(" ",A3)))+1)+1,2))

请注意，它是使用文本时间格式的单元格A3进行设置的。

3) 您还应该能够使用Excel的“数据”选项卡上位于中间位置的“文本到列”功能。

4) 当然，还可以通过VBA编码作为选项来完成此操作。

- Forward Ed

1

全日期时间转换的被接受答案过于复杂。 Excel 的 VALUE() 函数可以实现所需的转换，而无需分别调用 DATEVALUE() 和 TIMEVALUE()：

对于单元格 A1 中的“文本”日期（例如问题中提供的“3/24/2016 11:22:07 PM”）：

=VALUE(A1)

将返回相应的Excel日期时间值（大约为42453.97）。

请注意，VALUE() 依赖使用的区域设置。如果机器上使用的是美国日期格式，则上述转换将起作用，但在英国日期格式下则不起作用。 TEXT()、DATEVALUE() 和 TIMEVALUE() 也是如此，因此接受的答案在文本日期与当前区域设置不匹配的情况下也不起作用。

如果您的日期处于不受支持的格式中，则需要更改操作系统的区域设置或从原始文本中自行解析日期。

简而言之：只需使用VALUE()，而不是复杂的TEXT()、DATEVALUE() 和 TIMEVALUE() 的组合。

- Neil T

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- PaulG · Accepted Answer

日期转换：

=DATEVALUE(TEXT(A1,"MM/DD/YYYY"))

进行时间转换：

=TIMEVALUE(TEXT(A1,"HH:MM:SS"))

对于日期时间转换：

=DATEVALUE(TEXT(A1,"MM/DD/YYYY"))+TIMEVALUE(TEXT(A1,"HH:MM:SS"))

如果您希望将数据转换，可以使用A1中的数据。

顺便提一下，您可能希望将单元格格式设置为日期/时间或其他格式。

希望这能帮到您。