Calculate total hours between two dates and times

[Krupsdahl]

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..

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

<tbody>
</tbody>

This is the formulas I created

Column J=	IF(AND(G2="#";H2="#";I2="#");24;IF(AND(H2="#";I2="#");24-G2*24;IF(G2="#";I2*24;0)))
Column K=	IFERROR(IF(H2>B2;(I2-G2)*24+24;(I2-G2)*24);0)
Column L=	IF(AND(OR(G3="#");OR(G4="#";H4="#";I4="#"));J2+K2+J3+J4+J5;K2)
Column N=	IF(L2>=24;471/24*L2;0)

<tbody>
</tbody>

Best Regards, Tim

 

[steve the fish]

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.

 

[Mel Smith]

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

 

[wideboydixon]

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

 

[Krupsdahl]

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

 

[wideboydixon]

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

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

WBD

 

[Krupsdahl]

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 ?

 

[SpillerBD]

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

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)

 

[wideboydixon]

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 ?

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
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	CVR:	Start date:	Place:	Total Distance (km):	Earnings:	Travel hours:	Start time:	End date:	End time:	Calc.	Calc.	Total hours	Faktura nr.:	Dit:
2	*	13-Jun-16	Rasmus Walter - Produktionsver Portalen Greve	53.2	4,000.00 kr	1	08:00	13-Jun-16	23:00	0.0	15.0	15.0	162	0
3	*	18-Jun-16	Rasmus Walter - Knuthenborg Safari Live	290	4,000.00 kr	3.5	12:00	18-Jun-16	22:00	0.0	10.0	10.0	163	0
4	*	24-Jun-16	Rasmus Walter - Karolinelunden lborg	829.2	4,000.00 kr	8	10:00	25-Jun-16	08:00	0.0	22.0	22.0	164	0
5	*	20-Jul-16	Musik i Lejet	60	3,000.00 kr	1	16:00	#	#	8.0	0.0	8.0	165	0
6	*	21-Jul-16	Musik i Lejet	0	3,000.00 kr	0	#	#	#	24.0	0.0	32.0	165	628
7	*	22-Jul-16	Musik i Lejet	0	3,000.00 kr	0	#	#	#	24.0	0.0	56.0	165	1099
8	*	23-Jul-16	Musik i Lejet	60	3,000.00 kr	1	#	#	08:00	8.0	0.0	64.0	165	1256
9	*	28-Jul-16	Turboweekend - Svendborg	166	3,000.00 kr	2	10:00	#	#	14.0	0.0	14.0	166	0
10	*	29-Jul-16	Turboweekend - Grim Festival	487	3,000.00 kr	5	#	30-Jul-16	10:00	10.0	0.0	10.0	167	0
11	*	06-Aug-16	Rasmus Walter - Ringsted Festival	131	4,000.00 kr	2	08:00	06-Aug-16	17:00	0.0	9.0	9.0	168	0
Sheet1

Cell Formulas
Range	Formula
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