日期和时间的直接转换（20100528更新）

mxqchina

副标题：（60期函数竞赛题有感）
此帖产生的缘由：
[60期]各种格式的时间直接求总和

发现了@的妙用，今天就总结一下，也留下自己的学习的脚印。

首先看一个公式：

=--TEXT(NOW(),"e-m")

大家猜一下，公式返回什么日期？

答案是：当月1日

如果不用上述公式，用一个大家都比较熟悉的公式会长很多：

=DATE(YEAR(NOW()),MONTH(NOW()),1)

为什么会这样，下面的附件会给你解开小小的一角。

总结：

日期:
中文大写日期直接转换，全部失败。
"2010年5月24日"形式的，年月日齐全、月日（返回系统当年)、年月（返回当月1日）返回成功。
"2010-5-24"、"2010/5/24"和"24-MAY-2010"（日-月-年)，返回规律同上。

时间:
有点类似日期，但比日期少一个，只能时分秒齐全和时分可以转换，分秒无法转换。

日期+时间:
只要包含中文日期或时间都不行。形如"2010-5-24 16:17:35"和"2010-5-24 16:17"还是可以直接转换。

Text(单元格,"@")和=--单元格相比最大的好处就是，如果格式错误，不返回错误值，返回该文本。但请留意TEXT函数返回值均为文本格式。

附件内第一个表，可以筛选备注1为成功，看看那些形式属于下excel认同的规律，我们编制公式就往这方面靠。

实例部分少了点，大家一起来丰富。如果觉得不好，大家一起来修正。期待。

请下载最新附件（包含老附件所有内容）。老附件，只是让大家看到帖子的进步。

下载最后一个即可。

[ 本帖最后由 mxqchina 于 2010-5-28 10:10 编辑 ]

zhc19620430

在这里学习一会，感觉挺好。

laowuwyx

好东西，收藏慢慢学，谢谢版主。

zhc19620430

我对“@”和“--”的理解：
text（）函数本身是文本函数，奇妙之处必不可说。日期可以说是特殊数值。日期的文本形式一般使用“--”或“*”等都可以转换成数值，但正正的文本是无法转换成数值得。而恰恰“@”刚好就填补了文本和数值格式。所以就有了你的“日期和时间的直接转换”帖子。
    认识不足之处，还望谅解。

mxqchina

原帖由 zhc19620430 于 2010-5-24 21:52 发表
我对“@”和“--”的理解：
text（）函数本身是文本函数，奇妙之处必不可说。日期可以说是特殊数值。日期的文本形式一般使用“--”或“*”等都可以转换成数值，但正正的文本是无法转换成数值得。而恰恰“@”刚好就填 ...

正如我帖子里所说的 ，text+@如果碰到错误文本，直接返回该文本，而不是错误值。

zhc19620430

@应该就是返回文本，而不是“如果碰到错误文本，直接返回该文本”，文本就是文本，没有错误之说吧。

wangg913

拜读。
@这个参数在不讲究公式长度的时候，多数可用别的参数代替。
比如：
正整数+文本筛选，TEXT(,"0;;0;@")   TEXT(,"@") TEXT(,"?")
文本筛选，TEXT(,)  TEXT(,";;;@")

mxqchina

原帖由 zhc19620430 于 2010-5-25 09:09 发表
@应该就是返回文本，而不是“如果碰到错误文本，直接返回该文本”，文本就是文本，没有错误之说吧。

我的表达确实有误，此错误文本应该用excel无法直接转换为日期或时间的文本来表示。

zhc19620430

哦，大头作了解释，实际TEXT(,";;;@")里的；；；后面的就是文本格式，前面的是大于零，小于零和零表达式吧。
    有一个公式是判断数据类型的：text(a1,大于0;小于0";0;文本)
   还有判断a2日期是否升序排列的公式：=TXET(N(A3>=A2),";;日期有误;")

mxqchina

新增部分实例和测试。请下载最新附件。请大家多多提意见和想法。