文本与日期格式相互转换

1. 案例简介

本案例将介绍，如何通过函数进行文本与日期之前的格式转换。

2. 将日期转换为文本格式

2.1 方法一：TEXT函数

1）实现思路及所需函数

首先通过 DATE 函数将日期时间字段值转换为日期对象，再通过 TEXT 函数将日期对象转换为特定格式的文本。

2）效果演示

公式设置如下，其中 yyyy 表示年份，MM 表示月份，dd 表示日。

TEXT(DATE(日期时间),'yyyy-MM-dd')

公式设置如下，其中 HH 表示小时，mm 表示分钟，ss 表示秒。

TEXT(DATE(日期时间),'yyyy-MM-dd HH:mm:ss')

可以根据需要自由组合文本格式，如下所示，表示将日期时间转换为「年月日」格式的文本，更多时间格式可参考：TEXT(date, text_format)。

TEXT(DATE(日期时间),'yyyyMMdd')

点击体验效果

2.2 方法二：DATE函数

2.2.1 设置方式

1）实现思路及所需函数

分别通过各个日期函数，单独取出日期时间字段中的年、月、日等信息（此时取出的值为数字类型）。再通过 T EXT 函数将数字转换为文本，并使用 CONCATENATE 拼接为所需格式的文本即可。

2）效果演示

分别提取日期时间中的年、月、日信息，将其转换为文本格式，再将这些信息用 “-” 连接起来。公式设置如下：

CONCATENATE(TEXT(YEAR(DATE(日期时间))),'-',TEXT(MONTH(DATE(日期时间))),'-',
TEXT(DAY(DATE(日期时间))))

分别提取日期时间中的时、分、秒等信息，将其转换为文本格式，再将这些信息用 “-” 、 “ ” “:” 连接起来：

CONCATENATE(TEXT(YEAR(日期时间)),'-',TEXT(MONTH(日期时间)),'-',
TEXT(DAY(日期时间)),' ',TEXT(HOUR(日期时间)),':',TEXT(MINUTE(日期时间)),':',TEXT(SECOND(日期时间)))

点击体验效果

2.2.2 关于文本位数的补充

在使用 2.2.1 节方案，即使用 TEXT 函数将提取出的数字类型的月、日、时转换为文本时，会导致位数的缺失，例如，日期时间 2021-05-21 中的月份在转换时，05 仅能转换为文本 5。

因此，如需统一成 XXXX-XX-XX XX:XX:XX 的格式，需要对月份、日期、时、分、秒补足位数，在前面拼接一个 0。补充思路如下：

1）实现思路

对于转换得到的文本类型的月份、日期、时、分、秒，先在前侧拼接一个 0，再使用 RIGHT(a,b) （从字符串 a 的右边取字符，取 b 个）函数从右边取 2 位即可。以月份为例，公式配置及效果如下：

RIGHT('0'+TEXT(MONTH(DATE(时间日期))),2)

这样就实现了取出月份必定是 2 位数的格式。其余格式同理。

2）效果演示

CONCATENATE(TEXT(YEAR(DATE(日期时间))),'-',RIGHT('0'+TEXT(MONTH(DATE(日期时间))),2),'-',
RIGHT('0'+TEXT(DAY(DATE(日期时间))),2))

CONCATENATE(TEXT(YEAR(日期时间)),'-',RIGHT('0'+TEXT(MONTH(日期时间)),2),'-',
RIGHT('0'+TEXT(DAY(日期时间)),2),' ',RIGHT('0'+TEXT(HOUR(日期时间)),2),':',RIGHT('0'+TEXT(MINUTE(日期时间)),2),':',RIGHT('0'+TEXT(SECOND(日期时间)),2))

点击体验效果

3. 将文本转换为日期格式

1）实现思路及所需函数

首先通过 MID 分别取出文本中的年、月、日等信息，并转换为数值格式，再使用 DATE 转换为日期时间即可。

2）函数示例

分别提取出单行文本中的年、月、日信息，转换成数值格式，再转换为日期时间格式：

DATE(VALUE(MID(单行文本,1,4)),VALUE(MID(单行文本,5,2)),VALUE(MID(单行文本,7,2)))

分别提取出单行文本中的年、月、日、时、分、秒信息，转换成数值格式，再转换为日期时间格式：

DATE(VALUE(MID(单行文本,1,4)),VALUE(MID(单行文本,5,2)),VALUE(MID(单行文本,7,2)),VALUE(MID(单行文本,10,2)),VALUE(MID(单行文本,13,2)),VALUE(MID(单行文本,16,2)))

点击体验效果