Calculate total hours between two dates and times

K

Krupsdahl

New Member
Joined
Dec 12, 2012
Messages
33
Hi all..

I hope you can help me with this.. I have attached a test worksheet I did. But it seems that I got something wrong.. Or like I did it in a stupid way.

I was hoping that you could help me calculate the total hours spent away from my home, so I can put it in my sheet and turn in to my accountant..

So for instance if I left home at 10.00 the 28/2/16 and returned the 3/3/16 at 12.00 it should count 98 hours in column L. Be aware that not all months have the same length.

You do not need to concern about CVR, PLACE, TOTAL DISTANCE, EARNINGS, TRAVEL HOURS and FAKTURA NR..

[TABLE="width: 1246"]
<tbody>[TR]
[TD]CVR:[/TD]
[TD]Start date:[/TD]
[TD]Place:[/TD]
[TD]Total Distance (km):[/TD]
[TD]Earnings:[/TD]
[TD]Travel hours:[/TD]
[TD]Start time:[/TD]
[TD]End date:[/TD]
[TD]End time:[/TD]
[TD]Calc.[/TD]
[TD]Calc.[/TD]
[TD]Total hours[/TD]
[TD]Faktura nr.:[/TD]
[TD]Diæt:[/TD]
[/TR]
[TR]
[TD]*[/TD]
[TD]13. juni 2016[/TD]
[TD]Rasmus Walter - Produktionsøver Portalen Greve[/TD]
[TD]53,2[/TD]
[TD]4.000,00 kr[/TD]
[TD]1[/TD]
[TD]08.00[/TD]
[TD]13. juni 2016[/TD]
[TD]23.00[/TD]
[TD]0,0[/TD]
[TD]15,0[/TD]
[TD]15,0[/TD]
[TD]162[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]*[/TD]
[TD]18. juni 2016[/TD]
[TD]Rasmus Walter - Knuthenborg Safari Live[/TD]
[TD]290[/TD]
[TD]4.000,00 kr[/TD]
[TD]3,5[/TD]
[TD]12.00[/TD]
[TD]18. juni 2016[/TD]
[TD]22.00[/TD]
[TD]0,0[/TD]
[TD]10,0[/TD]
[TD]10,0[/TD]
[TD]163[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]*[/TD]
[TD]24. juni 2016[/TD]
[TD]Rasmus Walter - Karolinelunden Ålborg[/TD]
[TD]829,2[/TD]
[TD]4.000,00 kr[/TD]
[TD]8[/TD]
[TD]10.00[/TD]
[TD]25. juni 2016[/TD]
[TD]08.00[/TD]
[TD]0,0[/TD]
[TD]22,0[/TD]
[TD]22,0[/TD]
[TD]164[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]*[/TD]
[TD]20. juli 2016[/TD]
[TD]Musik i Lejet[/TD]
[TD]60[/TD]
[TD]3.000,00 kr[/TD]
[TD]1[/TD]
[TD]16.00[/TD]
[TD]#[/TD]
[TD]#[/TD]
[TD]8,0[/TD]
[TD]0,0[/TD]
[TD]64,0[/TD]
[TD]165[/TD]
[TD]1256[/TD]
[/TR]
[TR]
[TD]*[/TD]
[TD]21. juli 2016[/TD]
[TD]Musik i Lejet[/TD]
[TD]0[/TD]
[TD]3.000,00 kr[/TD]
[TD]0[/TD]
[TD]#[/TD]
[TD]#[/TD]
[TD]#[/TD]
[TD]24,0[/TD]
[TD]0,0[/TD]
[TD]70,0[/TD]
[TD]165[/TD]
[TD]1373,75[/TD]
[/TR]
[TR]
[TD]*[/TD]
[TD]22. juli 2016[/TD]
[TD]Musik i Lejet[/TD]
[TD]0[/TD]
[TD]3.000,00 kr[/TD]
[TD]0[/TD]
[TD]#[/TD]
[TD]#[/TD]
[TD]#[/TD]
[TD]24,0[/TD]
[TD]0,0[/TD]
[TD]56,0[/TD]
[TD]165[/TD]
[TD]1099[/TD]
[/TR]
[TR]
[TD]*[/TD]
[TD]23. juli 2016[/TD]
[TD]Musik i Lejet[/TD]
[TD]60[/TD]
[TD]3.000,00 kr[/TD]
[TD]1[/TD]
[TD]#[/TD]
[TD]#[/TD]
[TD]08.00[/TD]
[TD]8,0[/TD]
[TD]0,0[/TD]
[TD]0,0[/TD]
[TD]165[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]*[/TD]
[TD]28. juli 2016[/TD]
[TD]Turboweekend - Svendborg[/TD]
[TD]166[/TD]
[TD]3.000,00 kr[/TD]
[TD]2[/TD]
[TD]10.00[/TD]
[TD]#[/TD]
[TD]#[/TD]
[TD]14,0[/TD]
[TD]0,0[/TD]
[TD]0,0[/TD]
[TD]166[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]*[/TD]
[TD]29. juli 2016[/TD]
[TD]Turboweekend - Grim Festival[/TD]
[TD]487[/TD]
[TD]3.000,00 kr[/TD]
[TD]5[/TD]
[TD]#[/TD]
[TD]30. juli 2016[/TD]
[TD]10.00[/TD]
[TD]10,0[/TD]
[TD]0,0[/TD]
[TD]0,0[/TD]
[TD]167[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]*[/TD]
[TD]6. august 2016[/TD]
[TD]Rasmus Walter - Ringsted Festival[/TD]
[TD]131[/TD]
[TD]4.000,00 kr[/TD]
[TD]2[/TD]
[TD]08.00[/TD]
[TD]6. august 2016[/TD]
[TD]17.00[/TD]
[TD]0,0[/TD]
[TD]9,0[/TD]
[TD]9,0[/TD]
[TD]168[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]0,0[/TD]
[TD]0,0[/TD]
[TD]0,0[/TD]
[TD]169[/TD]
[TD]0[/TD]
[/TR]
</tbody>[/TABLE]

This is the formulas I created

[TABLE="width: 435"]
<tbody>[TR]
[TD]Column J=[/TD]
[TD="colspan: 2"]IF(AND(G2="#";H2="#";I2="#");24;IF(AND(H2="#";I2="#");24-G2*24;IF(G2="#";I2*24;0)))[/TD]
[/TR]
[TR]
[TD]Column K=[/TD]
[TD="colspan: 2"]IFERROR(IF(H2>B2;(I2-G2)*24+24;(I2-G2)*24);0)[/TD]
[/TR]
[TR]
[TD]Column L=[/TD]
[TD="colspan: 2"]IF(AND(OR(G3="#");OR(G4="#";H4="#";I4="#"));J2+K2+J3+J4+J5;K2)[/TD]
[/TR]
[TR]
[TD]Column N=[/TD]
[TD="colspan: 2"]IF(L2>=24;471/24*L2;0)[/TD]
[/TR]
</tbody>[/TABLE]


Best Regards, Tim
 

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).
Sort by date Sort by votes
Are your dates numerical dates or text dates? You will need to make them numeric. Then just add the end date & time and delete the start date and time then multiply by 24.
 
Upvote 0
Hi Tim,

I've come up with a possible solution - not particularly neat but it works.

I custom formatted a new cell as hh:mm dd/mm/yy and entered time of departure as, say 08:00 1/7/16 and using the same cell format for return time e.g. 15:00 3/7/16 and in another cell, formatted as [hh];mm, subtract the departure cell from the return cell.

HTH.

Mel
 
Upvote 0
Hi Tim,

After some pondering, I think I understand your data now. The problem you have is totalling up in column L; I assume you're happy with your calculations for J and K. Here's what I had in L2 copied down and I did actually use column M in order to determine whether it's part of the same total:

=SUMPRODUCT(--($M$2:$M2=$M2),$J$2:$J2+$K$2:$K2)

Alternatively you could use a couple of SUMIFs:

=SUMIF($M$2:$M2,$M2,$J$2:$J2)+SUMIF($M$2:$M2,$M2,$K$2:$K2)

WBD
 
Upvote 0
HI Guys.

Thank you for the replies.. I am not sure I can use either of your solutions. The whole meaning is that I want the start date, end date, start time, end time in 4 different columns.. and I want a clear calculation that counts the total hours.. and only in integers ( whole hours ) my two columns named Calc. and Calc. as well as total hours, i am not sure the formula is right...unfortunately
 
Upvote 0
Krupsdahl said:
HI Guys.

Thank you for the replies.. I am not sure I can use either of your solutions. The whole meaning is that I want the start date, end date, start time, end time in 4 different columns.. and I want a clear calculation that counts the total hours.. and only in integers ( whole hours ) my two columns named Calc. and Calc. as well as total hours, i am not sure the formula is right...unfortunately
Click to expand...

Did you try my solution? Is it not working for you?

WBD
 
Upvote 0
Wideboydixon. The problem is it is wrong colums, so i am not sure which ones you intended to use..

If you count my columns I have N columns in my test sheet.. can you redo that formula then ?
 
Upvote 0
Krupsdahl said:
HI Guys.

Thank you for the replies.. I am not sure I can use either of your solutions. The whole meaning is that I want the start date, end date, start time, end time in 4 different columns.. and I want a clear calculation that counts the total hours.. and only in integers ( whole hours ) my two columns named Calc. and Calc. as well as total hours, i am not sure the formula is right...unfortunately
Click to expand...

For the most part the calculation is straight forward, but the display(formatting) gets some people. Use the [h] in the formatting of the time.
You can round to the appropriate Hour after converting from Date/Time Serial number (ROUNDUP, ROUNDDOWN or MROUND)
=(EndDate+EndTime)-(StartDate+StartTime)
 
Upvote 0
Krupsdahl said:
Wideboydixon. The problem is it is wrong colums, so i am not sure which ones you intended to use..

If you count my columns I have N columns in my test sheet.. can you redo that formula then ?
Click to expand...

Did you try my formula in cell L2 and then copy it down? I tested on a copy of your sheet that I created. Apologies that I had to re-format the dates and numbers:


Book1
ABCDEFGHIJKLMN
1CVR:Start date:Place:Total Distance (km):Earnings:Travel hours:Start time:End date:End time:Calc.Calc.Total hoursFaktura nr.:Dit:
2*13-Jun-16Rasmus Walter - Produktionsver Portalen Greve53.24,000.00 kr108:0013-Jun-1623:000.015.015.01620
3*18-Jun-16Rasmus Walter - Knuthenborg Safari Live2904,000.00 kr3.512:0018-Jun-1622:000.010.010.01630
4*24-Jun-16Rasmus Walter - Karolinelunden lborg829.24,000.00 kr810:0025-Jun-1608:000.022.022.01640
5*20-Jul-16Musik i Lejet603,000.00 kr116:00##8.00.08.01650
6*21-Jul-16Musik i Lejet03,000.00 kr0###24.00.032.0165628
7*22-Jul-16Musik i Lejet03,000.00 kr0###24.00.056.01651099
8*23-Jul-16Musik i Lejet603,000.00 kr1##08:008.00.064.01651256
9*28-Jul-16Turboweekend - Svendborg1663,000.00 kr210:00##14.00.014.01660
10*29-Jul-16Turboweekend - Grim Festival4873,000.00 kr5#30-Jul-1610:0010.00.010.01670
11*06-Aug-16Rasmus Walter - Ringsted Festival1314,000.00 kr208:0006-Aug-1617:000.09.09.01680
Sheet1
Cell Formulas
RangeFormula
J2=IF(AND(G2="#",H2="#",I2="#"),24,IF(AND(H2="#",I2="#"),24-G2*24,IF(G2="#",I2*24,0)))
K2=IFERROR(IF(H2>B2,(I2-G2)*24+24,(I2-G2)*24),0)
L2=SUMPRODUCT(--($M$2:$M2=$M2),$J$2:$J2+$K$2:$K2)
N2=IF(L2>=24,471/24*L2,0)


WBD
 
Upvote 0
You must log in or register to reply here.

Similar threads

R
  • Question Question
Identifying the difference between two data sets
Replies
1
Views
165
igold
igold
Z
  • Solved
Calculate hours and minutes between two dates and time showing Early, Late, On Time
Replies
8
Views
1K
Fluff
Fluff
J
  • Question Question
Calculate difference between two dates showing days, hours, minutes in military time
Replies
3
Views
1K
6StringJazzer
6StringJazzer
A
  • Question Question
Find the difference in days, hours and minutes between two dates and times
Replies
5
Views
1K
Dave Patton
D
J
  • Solved
Calculating Minutes Between Date/Time with Operating Hours
Replies
8
Views
1K
jorismoerings
J

Forum statistics

Threads
1,223,228
Messages
6,170,871
Members
452,363
Latest member
merico17

Share this page

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
Back
Top