☰

在 Excel 2010 中将文本日期转换为标准日期格式

tag-icon • tag-icon microsoft-excel microsoft-excel-2010 date-time
在 Excel 2010 中将文本日期转换为标准日期格式

我有一个 Excel 2010 电子表格，其中输入了大量 2014 年的日期，格式如下：Thursday 24th July我需要将其转换为标准日期时间格式（最终结果将被导入到 SQL 表中，日期将类似于YYYYMMDD我上面提到的日期20140724，我可以处理该转换，但 Excel 不会识别Thursday 24th July为日期）。

答案1

好吧，这是一个相当丑陋的公式，我相信有人能想出一个更干净的公式。不过我已经测试过了，它有效，我想这才是最重要的。

在相邻的列中输入以下公式：

日期在 A2 中的起始位置

=DATEVALUE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(
  RIGHT($A2,LEN($A2)-FIND(" ",$A2)),"th","",1),"rd","",1),"st","",1"),"nd","",1))


DATEVALUE 将存储为文本的日期转换为 Excel 日期代码，以便可以正确格式化，但 7 月 24 日星期四这个日期存在一些问题；

  1. DATEVALUE 不能使用星期几，而且无论如何也不相关
  2. th或rd一天结束时的数字也是公式无法使用的值
  3. 此日期中没有存储年份

此公式无法对年份做任何处理，但它能做的是这样的（从中间向外计算）：


RIGHT($A2,LEN($A2)-FIND(" ",$A2))

通过找出文本日期的长度并减去开始和第一个空格（星期名称后的空格）之间的字符数来返回星期数和月份

SUBSTITUTE([FindText],"th","")

th将返回字符串中的任何实例替换为空

SUBSTITUTE([FirstSubstitute],"rd","")

rd将返回字符串中的任何实例替换为空

=DATEVALUE([StringReplacers])

将结果转换为日期。若未提供年份，则将假定为当前年份。

答案2

我在写这个问题时发现了一个解决方案，所以我想我还是会提交它，以防它对其他人有帮助：

这不是一个理想的解决方案，但它似乎有效。一些批量查找/替换操作将日期转换为 Excel 可以理解的日期。重要的是要注意空格，搜索时不要包含撇号，但要注意其中是否有空格

  1. 查找/替换、查找'Monday '并替换''、相同替换'Tuesday '等'Wednesday '以从日期中删除天数。
  2. 查找/替换、查找'January'并替换为'January 2014'、相同'February'等'March'以添加您想要的年份
  3. 查找/替换、查找'st '并替换为、' '相同替换和'nd ''rd ''th '

然后，您可以将单元格格式化为日期，Excel 应该会识别并转换它们。搜索/替换中的正确间距很重要，因为您不想找到'st'并'1st'让它删除末尾'August'

答案3

嗯，你可以用一些更短的语言：

=(IFERROR(MID(K32,FIND(" ",K32)+1,2)*1,MID(K32,FIND(" ",K32)+1,1))&
  MID(K32,FIND(" ",SUBSTITUTE(K32," ","",1))+1,100))*1

第一部分：

IFERROR(MID(K32,FIND(" ",K32)+1,2)*1,MID(K32,FIND(" ",K32)+1,1))

提取日期。它首先尝试提取第一个空格后的 2 个字符，如果该字符是数字（乘以 1 时不会返回错误），则将其添加到第二部分，即月份。

如果这是一个错误（例如，前两个字符1st是1s无效数字），那么这是一个小于 10 的日期，因此只提取一个字符。

MID(K32,FIND(" ",SUBSTITUTE(K32," ","",1))+1,100)

提取月份及其前面的空格。

然后将整个值乘以 1 以获得日期值。您可以yyyymmdd通过自定义格式进行格式化，然后日期就可以导入到 SQL 中了。

与 @CLockeWork 的公式一样，由于公式中未提及年份，因此工作表将采用当前年份。如果需要指定年份，则必须手动执行，例如，如下所示：

=(IFERROR(MID(K32,FIND(" ",K32)+1,2)*1,MID(K32,FIND(" ",K32)+1,1))&
  MID(K32,FIND(" ",SUBSTITUTE(K32," ","",1))+1,100)&" 2013")*1

2013 年。

答案4

您可以使用此公式转换为 2014 年的日期

=(REPLACE(REPLACE(A2,FIND(" ",A2,11)-2,2,""),1,FIND(" ",A2),"")&2014)+0

将返回日期序列号，格式为所需的日期格式

第一个FIND函数找到第 11 个字符或之后的第一个空格（考虑到星期几的长度，这必须是字符串中的第二个空格），然后在此基础上REPLACE删除紧接在前两个字符（将是“st”、“th”、“rd”或“nd”）

...然后第二个FIND找到第一个空格并REPLACE替换之前的所有文本，只剩下7月24日或类似

然后，您可以将年份 [2014] 连接到末尾，并使用 +0 强制转换为日期

如果你想直接转到20140724（作为字符串），你可以使用这个版本

=2014&TEXT(REPLACE(REPLACE(A2,FIND(" ",A2,11)-2,2,""),1,FIND(" ",A2),""),"mmdd")

相关内容