Excel日期/时间差 [英] Excel date/time difference
问题描述
我想计算出自指定日期和时间以来的当前时间.
I would like to work out the current time since a date and time specified.
如果指定的数据是:19/01/2013 16:44:00
现在的时间是:19/01/2013 16:45:00
If the specified data is : 19/01/2013 16:44:00
and now the time is : 19/01/2013 16:45:00
区别是0年0个月0天0小时1分钟0秒
the difference is 0 years 0 months 0 days 0 hours 1 minute 0 seconds
只需将当前时间减去上一个时间并将其格式化,就可以轻松实现,但这很难处理数据,因此我想做的就是将每个日期/时间段分成自己的单元格
This can be done easily just by taking the current time and minus the previous time and format it but this is hard to crunch data so what I wish to do is break each date/time segment into its own cell
假设单元格A1具有前一个时间,单元格A2具有当前时间
Let's say cell A1 has the previous time and cell A2 has the current time
a5槽f5单元格将包含0年0个月0天0小时1分钟0秒
Cells a5 trough f5 would contain 0 years 0 months 0 days 0 hours 1 minute 0 seconds
关于这个问题的第二点说明,是否可以通过VBA强制=NOW()
每隔x秒更新一次,而无需进行交互?
On a second note on this question is it possible to force the =NOW()
to update every x seconds via VBA without interaction?
推荐答案
您不能使用YEAR
函数获取年份差,因为这将在2012年12月31日至2013年1月1日之间给出1年,而只有一天的时间.....
You can't use YEAR
function to get year difference because that will give 1 year between 31st December 2012 and 1st Jan 2013 when there is only one day.....
....同样,您可能会遇到MONTH和DAY的问题(例如MONTH总是返回1到12之间的数字,而在这种情况下,您只会期望0到11之间的数字)
....similarly you can have problems with MONTH and DAY (e.g. MONTH always returns a number between 1 and 12 whereas in this context you would only expect numbers between 0 and 11)
尝试用DATEDIF
表示前3个,然后按贾斯汀的建议HOUR
,MINUTE
,SECOND
Try DATEDIF
for the first 3 then HOUR
, MINUTE
, SECOND
as Justin suggests
假设A2中的开始日期/时间和B2中的结束日期/时间分别尝试使用以下公式分别表示年,月,日,时,分和秒
Assuming start date/time in A2 and end date/time in B2 try these formulas respectively for Years, Months, Days, Hours, Minutes and Seconds
=DATEDIF(A2,B2-(MOD(A2,1)>MOD(B2,1)),"y")
=DATEDIF(A2,B2-(MOD(A2,1)>MOD(B2,1)),"ym")
=DATEDIF(A2,B2-(MOD(A2,1)>MOD(B2,1)),"md")
=HOUR(B2-A2)
=MINUTE(B2-A2)
=SECOND(B2-A2)
将所有单元格设置为常规格式
format all cells as general
由于月份/年份的长短不一,您可能会出现一些差异……
You may get some discrepancies because of variable month/year lengths......
这篇关于Excel日期/时间差的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!