EXCEL如何将文本日期转换为标准日期格式

在数据处理中，经常需要将文本格式的日期或以年月日形式存储的日期转换为Excel的标准序列号日期。本文将详细介绍如何通过工作表公式、数据工具中的“分列”功能以及Power Query实现这一转换，并补充相应的Excel函数或代码。

一、使用工作表公式进行转换

当日期以“年月日”格式（如“20230115”）存储为文本时，可以使用Excel的TEXT函数结合数学运算将其转换为标准序列号日期。以下是具体步骤：

使用TEXT函数格式化文本日期：

假设数据位于单元格F2，可以使用以下公式：

=TEXT(F2,"0000-00-00")+0

• TEXT(F2,"0000-00-00")：将数字或文本格式的日期（如“20230115”）转换为“YYYY-MM-DD”格式的文本（例如“2023-01-15”）。
• +0：通过数学运算将文本日期转换为Excel的序列号日期。
• 公式输入后，按Ctrl+Enter确认。

应用日期格式

转换后的序列号日期需要应用适当的日期格式。可以通过以下步骤设置：

• 选中单元格，右键选择“设置单元格格式”。
• 在“数字”选项卡中选择“日期”，并选择合适的日期格式（如“2023/01/15”）。
• 双击公式单元格并向下拖动以应用到其他行。

注意：TEXT函数输出的初始结果是文本值，需通过+0或其他数学运算（如*1）转换为数值型序列号日期。

二、使用“分列”功能进行原位转换

对于需要将整个列的文本日期直接转换为序列号日期的情况，可以使用Excel的“分列”功能。以下是操作步骤：

• 打开“分列”向导
• 选中包含文本日期的列（例如F:F）。
• 点击“数据”选项卡中的“分列”按钮（快捷键：Alt+A, E）。
• 进入“文本分列向导”。
• 直接完成转换

“分列”向导包含三个步骤，但对于标准格式的日期（如“2023-01-15”），默认设置通常已足够。

直接点击“完成”按钮，Excel会自动将文本日期转换为序列号日期。

如果日期格式为“年月日”（如“20230115”），需在向导的第三步选择“日期”并指定格式为“YMD”，然后点击“完成”。

更改存储位置（可选）

在向导的第三步，可以指定输出位置（例如H5），以避免覆盖原始数据。

示例公式（VBA等效操作）：

Range("F:F").TextToColumns Destination:=Range("H5"), DataType:=xlDelimited, FieldInfo:=Array(1, 5)

FieldInfo:=Array(1, 5)表示按“YMD”格式解析日期。

快捷键高效操作

使用快捷键Alt+A, E, F可快速完成分列操作，适合频繁使用的场景。

三、使用Power Query进行批量转换

Power Query是Excel中强大的数据转换工具，适合处理大量数据或外部导入的数据。以下是将文本日期转换为序列号日期的步骤：

• 导入数据
• 选中包含日期的Excel表格。
• 点击“数据”选项卡中的“从表格/区域”按钮，打开Power Query编辑器。
• 删除无关步骤
• 在Power Query编辑器中，删除不必要的自动生成的步骤（如“更改类型”），以确保从原始数据开始处理。
• 设置日期数据类型
• 选中日期列，点击数据类型下拉菜单，选择“日期”。
• Power Query会自动将文本日期（如“2023-01-15”）转换为序列号日期。

处理特殊格式

如果日期是以数字形式存储的“年月日”（如20230115），需先将其转换为文本格式：

在Power Query中，添加一个新步骤，将列转换为文本类型：

= Table.TransformColumnTypes(Source,{{"DateColumn", type text}})

然后添加步骤，将文本转换为日期类型：

= Table.TransformColumnTypes(#"Previous Step",{{"DateColumn", type date}}, "zh-CN")

注意：根据数据格式，可能需要指定区域设置（如“zh-CN”）以正确解析“YMD”格式。

加载结果

点击“关闭并加载”按钮，将转换后的数据加载到Excel工作表中。

可选择加载到现有表格或新表格。

四、注意事项

• 公式方法适合小规模数据处理，灵活但需手动设置格式。
• 分列方法适合快速原位转换，尤其是标准格式的日期。
• Power Query适合处理复杂或外部数据源，自动化程度高。
• 确保输入数据的格式一致，以避免转换错误。
• 对于非标准日期格式（如“2023年1月15日”），可能需要额外的文本处理函数（如LEFT、MID、RIGHT）进行预处理：

=DATE(LEFT(F2,4),MID(F2,6,1),RIGHT(F2,2))

通过以上方法，可以高效地将文本日期或年月日格式转换为Excel的标准序列号日期，满足不同的数据处理需求。