☰

Excel 日期时间格式问题，可能依赖于语言环境

tag-icon • tag-icon microsoft-excel formatting date-time cell-format
Excel 日期时间格式问题，可能依赖于语言环境

我正在处理比利时 Excel 365 工作簿。我刚刚导入了一个日期列表，如下所示：

dinsdag 5 december 2023 11:55:55
dinsdag 5 december 2023 11:55:56
dinsdag 5 december 2023 11:55:57

（“dinsdag” 表示星期二，其余的意思显而易见）

Excel 无法将其识别为日期时间，因此我想将日期时间转换为该格式，并使用该格式的描述来解释 Excel 要做什么。
不幸的是，这似乎不起作用：在另外两个单元格中，我输入Ctrl;并修改了时间“12:34:56”，我看到的是：

在此处输入图片描述

（单元格格式设置为[$-x-sysdate]dddd, mmmm dd, jjjj u:mm:ss，其中j代表“jaar”（“年”）和u“uur”（“小时”））

下面的单元格（“E3”）具有单元格格式u:mm:ss。

在我看来，这毫无意义：单元格“E3”明确指出单元格格式u:mm:ss显示时间为“12:34:56”，那么为什么我在单元格“E2”中看不到时间戳？（供您参考，我放大了单元格，但仍然没有看到时间戳）

此外，一旦我有了正确的格式，是否有一个=DateFromText()基于格式的函数，我可以使用，例如：

=DateFromText(A1, "[$-x-sysdate]dddd, mmmm dd, jjjj u:mm:ss")

提前致谢

答案1

尝试这个： =--REPLACE(A1,1,FIND(" ",A1),)

它从字符串中删除“Dinsdag”（或任何“-dag”）并将其转换为数值。

然后您可以根据需要对其进行格式化，例如：dddd d mmmm e hh:mm:ss

在此处输入图片描述

我不确定这是否适用于所有系统语言。如果这对您有用，请告诉我。

答案2

可能有更好的方法可以做到这一点，但您可以使用自定义 LAMBDA 函数将文本转换为实际日期：

=LAMBDA(_input, LET(
_array, TEXTSPLIT(_input, " "), 
_day, INDEX(_array, 2),
_month, MATCH(INDEX(_array, 3), {"januari","februari","maart","april","mei","juni","juli","augustus","september","oktober","november","december"}, 0),
_year, INDEX(_array, 4),
_time, INDEX(_array, 5),
DATE(_year, _month, _day)+TIMEVALUE(_time)
))

或者如果你的系统分隔符是“；”而不是“，”：

=LAMBDA(_input; LET(
_array; TEXTSPLIT(_input; " "); 
_day; INDEX(_array; 2);
_month; MATCH(INDEX(_array; 3); {"januari";"februari";"maart";"april";"mei";"juni";"juli";"augustus";"september";"oktober";"november";"december"}; 0);
_year; INDEX(_array; 4);
_time; INDEX(_array; 5);
DATE(_year; _month; _day)+TIMEVALUE(_time)
))

要添加此功能，只需转到名称管理器并添加一个新的命名范围，并以公式作为其值：

在此处输入图片描述

然后，只需在电子表格中调用该函数：

在此处输入图片描述

请注意，结果是真实的日期/时间，您可以根据自己的喜好更改显示格式。

该函数的工作原理如下：它将文本作为参数，并将其拆分为一个数组，使用空格作为分隔符。因此数组包含：

  1. 当天的名称（您不需要它）
  2. 数字表示的日期
  3. 月份名称
  4. 以数字表示的年份
  5. 时间格式为 hh:mm:ss

使用 INDEX 函数访问这 5 个值，您可以使用 DATE() 函数重新创建日期，并使用 TIMEVALUE() 函数将文本格式的时间转换为实际时间格式。添加 DATE 和 TIMEVALUE 会创建一个日期/时间。

请注意，需要使用 MATCH 函数将月份名称转换为数字。我手动添加了比利时语名称，但可以轻松编辑这 12 个值以匹配任何语言。

有关 lambda 函数的更多信息：https://exceljet.net/functions/lambda-function

相关内容