Date and time calculation is giving an incorrect answer

[Lyn Radnell]

HI,

The following formula seems to be incorrect. I typed in the times and dates to get the difference in hours, but my times are in 5 minute blocks, so the answer is given doesnt add up

<TABLE style="WIDTH: 371pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=495><COLGROUP><COL style="WIDTH: 145pt; mso-width-source: userset; mso-width-alt: 6855" width=193><COL style="WIDTH: 117pt; mso-width-source: userset; mso-width-alt: 5546" width=156><COL style="WIDTH: 109pt; mso-width-source: userset; mso-width-alt: 5176" width=146><TBODY><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 145pt; HEIGHT: 14.4pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl67 height=19 width=193>Start Date</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 117pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl68 width=156>Finsh date </TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 109pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl67 width=146>Hours</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl69 height=19>15/11/2011 13:50</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl69>17/11/2011 2:25</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl66 align=right>36.58</TD></TR></TBODY></TABLE>

If you can help, I would appreciate it.

Many thanks
Lyn

 

[Marcelo Branco]

Hi Lyn,

The result is correct since 0,58333 of one hour (ie multiplied by 60) = 35 minutes

To check try this formula

=INT((B2-A2)*24)&" hours and "&ROUND(MOD((B2-A2)*24,1)*60,0)&" minutes"

that returns

36 hours and 35 minutes

M.

 

[Peter_SSs]

Alternatively, you may want this: Just do the subtraction and format the formula cell with the Custom number format:
[hh]:mm

Excel Workbook
	A	B	C
1	Start Date	Finsh date	Hours
2	15/11/11 13:50	17/11/11 2:25	36:35
Time Diff

 

[Marcelo Branco]

Alternatively, you may want this: Just do the subtraction and format the formula cell with the Custom number format:
[hh]:mm

Excel Workbook
	A	B	C
1	Start Date	Finsh date	Hours
2	15/11/11 13:50	17/11/11 2:25	36:35
Time Diff

Much better and simpler!

M.