Date and time calculation is giving an incorrect answer

Lyn Radnell

New Member
Joined
Sep 4, 2011
Messages
7
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
 

Some videos you may like

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.

Marcelo Branco

MrExcel MVP
Joined
Aug 23, 2010
Messages
16,407
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

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
47,926
Office Version
  1. 365
Platform
  1. Windows
Alternatively, you may want this: Just do the subtraction and format the formula cell with the Custom number format:
[hh]:mm

Excel Workbook
ABC
1Start DateFinsh dateHours
215/11/11 13:5017/11/11 2:2536:35
Time Diff
 

Watch MrExcel Video

Forum statistics

Threads
1,122,517
Messages
5,596,619
Members
414,081
Latest member
Subaru_Steve

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top