Excel日期/时间计算 [英] Excel date/time calculation
问题描述
我正在计算两种以下格式的差异:
Fri 07/02/2014 10: 16
与单元格使用自定义格式ddd dd / mm / yyyy hh:mm
并以小时显示结果。
我有两个日期/时间:
A1 B1
Fri 07 / 02/2014 10:00 Fri 07/02/2014 10:42
C1是
= TEXT(TIME(LEFT(RIGHT(B1,5),2),RIGHT(RIGHT(B1,5),2 ),0)-TIME(LEFT(RIGHT(A1,5),2),RIGHT(RIGHT(A1,5),2),0),h)
哪个给我答案4,我认为应该是像0.68这样的东西
任何人都可以帮忙?
谢谢
更新:
A1实际上是这种格式 07/02/2014 10:00:00
,然后显示为 Fri 07/02/2014 10:00
/ p>
B1输入为'Fri 07/02/2014 10:42'
和d显示为'Fri 07/02/2014 10:42'
请尝试:
= DATE(MID(B1,11,4),MID(B1,8,2),MID (B1,5,2))+(MID(B1,16,2)+ RIGHT(B1,2)/ 60)/ 24-A1
并将单元格格式化为[hh]:mm。
I'm trying to calculate the difference between two times that are given in this format:
Fri 07/02/2014 10:16
with the cell using a custom format ddd dd/mm/yyyy hh:mm
and display the result in hours.
I have two dates/times:
A1 B1
Fri 07/02/2014 10:00 Fri 07/02/2014 10:42
The formula I have in C1 is
=TEXT(TIME(LEFT(RIGHT(B1,5),2),RIGHT(RIGHT(B1,5),2),0)-TIME(LEFT(RIGHT(A1,5),2),RIGHT(RIGHT(A1,5),2),0),"h")
Which gives me the answer 4, which I thought should be something like 0.68
Can anyone help?
Thanks
UPDATE:
A1 is actually in this format 07/02/2014 10:00:00
, which is then displayed as Fri 07/02/2014 10:00
B1 is entered as 'Fri 07/02/2014 10:42'
and displayed as 'Fri 07/02/2014 10:42'
Please try:
=DATE(MID(B1,11,4),MID(B1,8,2),MID(B1,5,2))+(MID(B1,16,2)+RIGHT(B1,2)/60)/24-A1
and format the cell as [hh]:mm.
这篇关于Excel日期/时间计算的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!