Number of Hours & Minutes between two Dates & Times. 1 min. Difference

Hey!

So, I figured out how to calculate the Duration/Number or Hours and Minutes between two dates & times using the following Formula:

A1 = 6/27/2019 | A2 = 10:00
B1 = 6/29/2019 | B2 = 6:30

C1 = To get the Hours =INT(((A1+A2)-(B1+B2))*24) Which would Result to = 44 hours
C2 = To get the Minutes =INT(((A1+A2)-(B1+B2))*1440)-(INT(((A1+A2)-(B1+B2))*24)*60) Which would Result to = 30 Minutes
(First part is calculating the number of Minutes then it subtract Hours in order to get clean result)


Problem is, It's Not accurate!


Real Example:

While the Above showed a correct 44 Hours and 30 Minutes, Following Date & Time shows incorrect result with the same formula:

A1 = 6/25/2019 | A2 = 15:00
B1 = 6/27/2019 | B2 = 7:00

C1 = 39 Hours | C2 = 59 Minutes

Where it should be 40 Hours.

A1 = 6/23/2019 | A2 = 1:30
B1 = 6/23/2019 | B2 = 2:30

C1 = 0 Hours | C2 = 59 Minutes

Where it should be 1 Hour.

It's not consistent, sometimes it would give a correct result, others it would Minus a minute which creates a problem with long records.
End goal is to Sum the Number of hours and Minutes.

File Attached, Dates are in Column [D] - Times are in Column [E] - Hours Formula in [F] - Minutes in [G]

Help is greatly appreciated.
Or if there's any other accurate method, please do say it!

To best describe or illustrate your problem you would be better off attaching a dummy workbook. The workbook should contain the same structure and some dummy data of the same type as the type you have in your real workbook - so, if a cell contains numbers & letters in this format abc-123 then that should be reflected in the dummy workbook.

To attach a file to your post,
click advanced (next to quick post),
scroll down until you see "manage file",
click that and select "add files" (top right corner).
click "select files" find your file, click "open" click "upload" click 'done" bottom right. click "submit reply"

Once the upload is completed the file name will appear below the input boxes in this window.
You can then close the window to return to the new post screen.

Originally Posted by torachan

First observation before checking anything - get rid of all merged cells - they play havoc with calcs.

Done that, still inaccuracy occurs.

For some reason, I'm suspecting decimals.

When I add 1 Minute to Time, Say 10:01, the Results wouldn't change in Minutes formula = 00:00
But! when I change it to 10:02, Minutes formula would record 1 Minute = 00:01

Again, inconsistent, Sometimes it'll give accurate results, others it won't.

How to get it Accurate 100% is what's frustrating

Or try this:

=ROUNDDOWN((D4-D3+E4-E3)*24,0)

and this:

=(((D4-D3+E4-E3)*24)-ROUNDDOWN((D4-D3+E4-E3)*24,0))*60

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

I've already tested my suggestions. If your sample data is properly representative, they will work.

Don't split the hour and minutes in 2 columns.
Keep them in 1 and use the next formula

Please Login or Register  to view this content.

So, whoever stumbles upon this post:

Most guides when searching on google will recommend using the formulas in my Original post,
But won't state that there will be a Minute difference in certain log events.

---------------------------

Phuocam Hours formula stores it as text which can be fixed by adding it within =NUMBERVALUE()
and Minutes formula worked.

AliGW Formulas work as well, although in some cases, Minutes formula can result into (E-) Numbers
Happened when tried to calculate this Event:

6/18/2019 | 20:30
6/20/2019 | 18:30

Probably I did something wrong, change the cell formatting to Number instead of General.
Corrected by AliGW on below post.

So I basically combined AliGW Hours Formula with Phuocam Minutes Formula. and they both work

torachan & popipipo Methods both helps if you don't want to separate the Hours from the Minutes by changing the Cell format to [Time -> 37:30:55]
Which could help in summary reports for ease of viewing.

---------------------------

To sum the total difference of Hours & Minutes I used the Following Formulas:

Total Hours : =(Hours)+(INT((Minutes)/60))
Total Minutes : =(Minutes)-(INT((Minutes)/60)*60)

What it'll do, if the Minutes are above 60, It'll convert them into Hours in the Total Hours Formula and Subtract them in Total Minutes Formula.


---------------------------

So far it's solved! No more 1 Minute Difference.

Thank you all for the help!

I'll report back if any issue occurred.

It works perfectly as long as you set the cells with the formula to number format (not general):

Excel 2016 (Windows) 32 bit

	B	C	D	E	F
2	18/06/2019	20:30		46	0
3	20/06/2019	18:30

Sheet: Sheet1

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.