excel学习库

这种10/8/2022 10:11:30.323通过数据库导出的数据，日期时间格式是纯文本，无法通过“ 右击 -设置单元格式”变成YYYY-MM-DD HH:MM:SS日期时间格式，只能通过函数，且需要考虑日、月这个怎么从1个字符串变成2个，比如5变成05形式

一、变成“YYYY-MM-DD HH:MM:SS ”格式

(一) 套用公式

=TEXT(DATE(VALUE(MID(N4,FIND("/",N4,FIND("/",N4)+1)+1,4)),VALUE(MID(N4,FIND("/",N4)+1,FIND("/",N4,FIND("/",N4)+1)-FIND("/",N4)-1)),VALUE(LEFT(N4,FIND("/",N4)-1))),"yyyy-mm-dd")&" "&MID(N4,FIND(" ",N4)+1,8)

N4 是纯文本日期所在的单元格

(二) 公式分析

这个公式可以分为两部分：

1. 日期部分：

TEXT(
DATE(
VALUE(MID(N4,FIND("/",N4,FIND("/",N4)+1)+1,4)), // 年份
VALUE(MID(N4, FIND("/",N4)+1,FIND("/",N4, FIND("/",N4)+1)-FIND("/",N4)-1)), // 月份
VALUE(LEFT(N4,FIND("/",N4)-1)) // 日
),
"yyyy-mm-dd" // 输出日期的格式
)

1. VALUE(LEFT(N4,FIND("/",N4)-1))：找到日期字符串中第一个斜杠之前的部分（即日），然后用 VALUE 函数将其转换为数值。

2. VALUE(MID(N4, FIND("/",N4)+1,FIND("/",N4, FIND("/",N4)+1)-FIND("/",N4)-1))：找到日期字符串中两个斜杠之间的部分（即月份），然后用 VALUE 函数将其转换为数值。

3. VALUE(MID(N4,FIND("/",N4,FIND("/",N4)+1)+1,4))：找到日期字符串中第二个斜杠之后的部分（即年份），然后用 VALUE 函数将其转换为数值。

4. DATE(年, 月, 日)：将年、月、日组合成日期。

5. TEXT(日期, "yyyy-mm-dd")：将日期格式化成 "YYYY-MM-DD" 的形式。

6. 时间部分：

MID(N4,FIND(" ",N4)+1,8)

1. FIND(" ",N4)+1：找到第一个空格之后的字符位置，即时间部分的起始位置。

2. MID(N4,FIND(" ",N4)+1,8)：从第一个空格之后的位置开始，提取 8 个字符，即时间部分。

最后，用 & 运算符将日期部分和时间部分连接起来。

二、变成“YYYYMMDDHHMMSS”格式

(一) 套用公式

=TEXT(DATE(VALUE(MID(N4,FIND("/",N4,FIND("/",N4)+1)+1,4)),VALUE(MID(N4,FIND("/",N4)+1,FIND("/",N4,FIND("/",N4)+1)-FIND("/",N4)-1)),VALUE(LEFT(N4,FIND("/",N4)-1))),"yyyymmdd")&TEXT(MID(N4,FIND(" ",N4)+1,8),"hhmmss")

N4 是纯文本日期所在的单元格，此公式跟第一个公式区别，只是在于text文本输入，将yyyy-mm-dd改成yyyymmdd，同理时间也变

三、变成“YYYY-MM-DD HH:MM”格式

(一) 套用公式

=TEXT(DATE(VALUE(MID(N4,FIND("/",N4,FIND("/",N4)+1)+1,4)),VALUE(MID(N4,FIND("/",N4)+1,FIND("/",N4,FIND("/",N4)+1)-FIND("/",N4)-1)),VALUE(LEFT(N4,FIND("/",N4)-1))),"yyyy-mm-dd")&" "&LEFT(MID(N4,FIND(" ",N4)+1,8),5)

(二) 公式分析

1. 日期部分：

日期部分是跟第一个公式一样的，时间部分有点区别

1. 时间部分：

excel
复制代码
LEFT(MID(N4,FIND(" ",N4)+1,8),5)

1. FIND(" ",N4)+1：找到第一个空格之后的字符位置，这个位置是时间部分的起始位置。

2. MID(N4,FIND(" ",N4)+1,8)：从第一个空格之后的位置开始，提取 8 个字符，即时间部分。

3. LEFT(时间部分,5)：提取时间部分的前 5 个字符，即 "HH:MM"。

四、变成“YYYY-MM-DD”格式

(一) 套用公式

=TEXT(DATE(VALUE(MID(N4,FIND("/",N4,FIND("/",N4)+1)+1,4)),VALUE(MID(N4,FIND("/",N4)+1,FIND("/",N4,FIND("/",N4)+1)-FIND("/",N4)-1)),VALUE(LEFT(N4,FIND("/",N4)-1))),"yyyy-mm-dd")