551 实用指南:表格中日期格式的规范录入与转换技巧

createh512小时前技术教程10

全文约1478字;

阅读时间:约4分钟;

听完时间:约8分钟;

日常工作中,在表格录入日期是一个非常高频的工作场景,而在表格中录入日期也是有严格定义的,录入不规范将会造成后续数据分析困难。在WPS和Excel中,日期实际上是以序列号的形式存储的。这个序列号代表了从1900年1月1日(或在某些系统设置下是从1904年1月1日开始)到指定日期之间的天数。

通俗的说就是数字1代表1天,转成日期格式的话就是1900年1月1日,同理1小时就是1除24,1分钟就再除60,1秒钟再除60。而2024年1月1日在Excel中的序列号是45292(基于1900年的日期系统),表示从1900年开始到今天累计了四万多天了。这种存储方式允许Excel进行日期计算,比如加减天数、月份或年份。

如果日期填写不规范,则需要将其转换为标准化的日期格式。转换的方法有多种,今天我将分享几种实用的转换方法。

替换方法

A列中的日期录入不规范,不是标准的日期格式,如:{"9.29";"10/11";"10月11号";"10.11";"10月12日"},需要对其标准化转成类似“2024/10/11”这样标准的YYYY/MM/DD的格式。可以在B2录入对应的公式并向下填充:

=SUBSTITUTE(SUBSTITUTE(A2,".","/"),"号","日")*1

公式解释:

SUBSTITUTE(A2, ".", "/"):首先替换A2单元格中所有的点(.)为斜杠(/),以适应标准日期格式。

SUBSTITUTE(..., "号", ""):接着去除可能存在的中文“号”字。

*1:通过乘以1强制Excel重新解析文本字符串为数值,进而根据系统设置自动识别并转换为正确的日期格式。需要注意的是,这样直接乘以1可能会导致年份丢失或错误识别的问题,因此更推荐使用DATEVALUE函数来确保正确解析日期。

正则方法

无论是WPS还是Excel,都提供了正则表达式相关的函数。使用正则表达式来处理不规范的日期格式是一种非常有效的方法。以下是在WPS中使用正则表达式的示例公式:(注意Excel的正则函数和WPS不同)

=TEXTJOIN("-",,2024,REGEXP(A2,"\d+"))*1

公式解释:

REGEXP(A2, "\d+"):在A2单元格的内容中查找所有连续的数字(\d+表示一个或多个数字)。这将提取出日期中的数字部分。

TEXTJOIN("-", , 2024, ...):将提取到的数字与年份2024用连字符“-”连接起来。参数表示忽略空值。

* 1:通过乘以1强制WPS将结果重新解析为日期。这样可以确保输出的是一个标准的日期格式。

AI方法

如果数据量不大,可以使用AI工具来进行日期格式的转换。打开任意AI工具,在对话框中录入以下内容(可以提前将表格中的数据复制并按F9转成内存数组,然后粘贴到下方):

“请将以下内容转换成标准的日期格式(YYYY-MM-DD),并在一维表格形式中显示,年份为2024年:

{"9.20";"9/27";"9.27";"9/29";"9.29";"10/11";"10月11号";"10.11";"10月12日";"10/12";"10月13号";"10.13";"10月18日";"10/18";"10/20";"10月20日";"10月20号"}”

通过AI工具可以自动将日期转换成标准格式。转换完成后,直接将结果粘贴到表格中即可。这种方法的优点是不需要手动编写公式,但缺点是如果有新的数据添加进来,这些新数据将不会自动转换成所需的格式。



最后总结

在日常工作中,正确录入和处理日期数据是非常重要的。不规范的日期格式不仅会增加后续数据分析的难度,还可能导致错误的结果。WPS和Excel中,日期以序列号的形式存储,这种存储方式使得日期计算变得简单高效。为了确保日期数据的一致性和准确性,我们介绍了几种实用的转换方法:

替换方法:通过使用SUBSTITUTE函数替换不规范的字符,并结合*1或DATEVALUE函数将文本字符串解析为正确的日期格式。

正则方法:利用正则表达式提取日期中的数字部分,并通过TEXTJOIN函数将其与年份连接起来,形成标准的日期格式。

AI方法:对于数据量较小的情况,可以使用AI工具自动完成日期格式的转换。这种方法操作简便,但新数据需要手动重新转换。

每种方法都有其优缺点。替换方法和正则方法虽然需要编写公式,但能够实现自动化处理;而AI方法虽然方便快捷,但无法实时同步处理新数据。选择合适的方法取决于具体的工作场景和个人偏好。无论采用哪种方法,确保日期数据的规范性和一致性是提高工作效率和准确性的关键。

相关文章

java 判断Date是上午还是下午

我要用Java生成表格统计信息,如下图所示:所以就诞生了本文的内容。在 Java 里,判断 Date 对象代表的时间是上午还是下午有多种方式,下面为你详细介绍不同的实现方法。方式一:使用java.ut...

吊打面试官(十一)--Java语言中日期处理相关问题总结

导读在Java中,日期处理是必然遇到的场景,也是问题较多的场景。下面我们将收集的日期问题场景做一个汇总。祝大家面试必过,吊打面试官。让我们0帧起手,开始说具体问题。1. 日期格式化大小写问题问题描述:...

SpringBoot 日期格式化的两种方式

在Spring Boot中处理日期格式化是一个常见的需求,涉及数据库存储、接口参数传递、序列化等场景。以下是综合多个实践和最佳方案的总结:一、全局日期格式化配置通过配置文件统一设置全局日期格式,适用...

玩转MySQL的时间类型:Date、DateTime、TimeStamp、Time

Mysql时间类型介绍在MySQL中,有四种不同的时间类型:DATE、DATETIME、TIMESTAMP和TIME。它们分别表示不同精度和范围的时间。DATE类型DATE类型用于存储日期,格式为'Y...

Java编程的那些屎山代码分析之一

在编程行业干过这么多年,已经学会了从代码看人,代码会告诉我们,这个人在这一行能否吃饱饭,能吃到多少肉。以下是个人总结的一些代码习惯问题和优化,单独一个也许不起眼,但堆积起来,就让一个项目代码变成一座屎...

日期和时间,你真的会输入吗?

日期和时间是Excel中一种特殊类型的数据,有关日期和时间的计算在各个领域中都有非常广泛的应用。以下讲解日期和时间类数据的特点及计算方法,以及日期与时间函数的相关应用。1. 输入日期数据按组合键,可以...