日期格式非标准, 如何计算周期?

wangjguo44 · 发表于 2013-7-18 19:28

本帖最后由 wangjguo44 于 2013-7-19 10:03 编辑

把楼上几位老师的公式汇总一下，并与转换成标准格式的辅助列进行比较，指出发现的错误。
（标准格式时间转换的对不对，只好目测对比，着重可查那些含“PM12:xx ”和“AM12:xx ”的日期时间转的对不对，因为在这两情况下往往出错）
经cleverzhzhf 老师提醒，原始数据中AM12:xx应该是表示凌晨0点~1点之间的时间，汇总表见16楼。（本楼附件仍旧保留不删以作对比参考）

准提部林 · 发表于 2013-7-18 22:41

wangjguo44 发表于 2013-7-18 19:28
把楼上几位老师的公式汇总一下，并与转换成标准格式的辅助列进行比较，指出发现的错误。
（标准格式时间转 ...

wangjguo44 老師真细心！
公式稍修，看看可否：

=MMULT(--(REPLACE(A2:B2,FIND(" ",A2:B2)+1,2,)&MID(SUBSTITUTE(A2:B2,"AM12","PM12"),FIND(" ",A2:B2),3)),{-1;1})

复制代码

wangjguo44 · 发表于 2013-7-19 07:16

本帖最后由 wangjguo44 于 2013-7-19 09:57 编辑

准提部林发表于 2013-7-18 22:41
wangjguo44 老師真细心！
公式稍修，看看可否：

减几个字符

=SUM((REPLACE(A2:B2,FIND(" ",A2:B2)+1,2,)&MID(SUBSTITUTE(A2:B2,"AM12","PM12"),FIND(" ",A2:B2),3))*{-1,1})

复制代码

如果原始数据中AM12:xx是表示凌晨0点~1点之间的时间的话，

=SUM((REPLACE(A498:B498,FIND(" ",A498:B498)+1,2,)&MID(A498:B498,FIND(" ",A498:B498),3))*{-1,1})

复制代码

cleverzhzhf · 发表于 2013-7-19 08:53

wangjguo44 发表于 2013-7-18 19:28
把楼上几位老师的公式汇总一下，并与转换成标准格式的辅助列进行比较，指出发现的错误。
（标准格式时间转 ...

老先生，您的式子附件好像也有点问题。
以480行为例
13-7-14 PM12:49换成标准时间是13/7/14 12:49
13-7-17 AM12:59换成标准时间是13/7/17 12:59

在这里PM和AM就没用了。。。实际上应该相差12小时。还请您再审核一下

cleverzhzhf · 发表于 2013-7-19 09:01

本帖最后由 cleverzhzhf 于 2013-7-19 09:12 编辑

wangjguo44 发表于 2013-7-18 19:28
把楼上几位老师的公式汇总一下，并与转换成标准格式的辅助列进行比较，指出发现的错误。
（标准格式时间转 ...

另外，还有个有趣的现象：
拿E列和J列对照，看一下199、301、434行：
如果保留三位小数，K列判定列显示FALSE
如果保留四位及以上位小数，则显示TRUE

浮点精度伤不起呀。。。

在您的附件上进行了修改，增加了将A2修改为SUBSTITUTE(A2,"AM12","AM0")，并且保留5位小数：

lyliyu · 发表于 2013-7-19 09:16

非常感谢各位大侠帮助, 我需要仔细学习学习

wangjguo44 · 发表于 2013-7-19 09:18

cleverzhzhf 发表于 2013-7-19 08:53
老先生，您的式子附件好像也有点问题。
以480行为例
13-7-14 PM12:49换成标准时间是13/7/14 12:49

您好，我是这样理解原数据的13-7-14 PM12:49指的是中午12点多，而不是深夜0点多，同样13-7-17 AM12:59也是中午12点多——如果这样理解错的话，那你说的是对的，谢谢

cleverzhzhf · 发表于 2013-7-19 09:29

wangjguo44 发表于 2013-7-19 09:18
您好，我是这样理解原数据的13-7-14 PM12:49指的是中午12点多，而不是深夜0点多，同样13-7-17 AM12:59也是 ...

可以用EXCEL现有的格式来验证，设置单元格为日期格式中的以下方式：
搜狗截图_2013-07-19_09-26-12.png

然后在A1输入=1+1/48
然后以下每格增加一小时，可以看一下13行和25行日期：
搜狗截图_2013-07-19_09-24-40.png

wangjguo44 · 发表于 2013-7-19 09:52

本帖最后由 wangjguo44 于 2013-7-19 10:04 编辑

cleverzhzhf 发表于 2013-7-19 08:53
老先生，您的式子附件好像也有点问题。
以480行为例
13-7-14 PM12:49换成标准时间是13/7/14 12:49

经11楼cleverzhzhf 老师提醒，原始数据中AM12:xx应该表示凌晨0点~1点之间的时间，那么3楼和7楼的公式是正确的，而经过我的“误导”，9楼可能改了反而错了，在此一并致歉。

EH(WJg)b.rar (240.13 KB, 下载次数: 4)

pxiceplay · 发表于 2013-7-19 16:19

准提部林发表于 2013-7-18 16:09
试试．Ｃ２一般公式：

mmult这里用的太棒了！在这基础上稍微改了一下
C2=MMULT(REPLACE(A2:B2,FIND(" ",A2:B2)+1,2,)+COUNTIF(A2:B2,"*P*")/2,{-1;1})

		自动登录	找回密码
密码			免费注册

[求助] 日期格式非标准, 如何计算周期?

评分

评分

评分