Calculating time difference when time starts before 00:00 and finishes after 00:01

[Drexl88]

Hi,

I am using the below to calculate time spent on certain activities by staff and to calculate the total time on task for all staff. The issue I have is that when the time commenced is before 00:00 and the time ceased in after 00:01 the formula does not work in F11. Some staff will be carrying out tasks starting for example at 23:30 and finishing at 01:30. What formula can I use so that this time allocated works in any event.

Thanks

Enhanced Hours Record (1).xlsx
	A	B	C	D	E	F
8	Date	Activity	From	To	No. of Staff	Total Time
9	06/01/2020		08:15:00	08:45:00	2	01:00:00
10	08/01/2020		23:15:00	23:30:00	1	00:15:00
11	12/01/2020		23:45:00	00:30:00	3	########
Jan 2020

Cell Formulas
Range	Formula
F9:F11	F9	=E9*(D9-C9)

Cells with Data Validation
Cell	Allow	Criteria
B9:B30,I9:I30	List	=Sheet2!$A$2:$A$8
C9:D30,J9:K30	List	=Sheet2!$B$2:$B$97
E9:E30,L9:L30	List	=Sheet2!$C$2:$C$6

 

[jmacleary]

Try this formula in F9:F11 (XL2BB has hung my copy of excel!)

=E9*MOD((1+D9-C9),1)

 

[Dave Patton]

2 alternatives;either should give the result

T202002a.xlsm
	A	B	C	D	E	F	G
8	Date	Activity	From	To	No. of Staff	Total Time	Total Time
9	06-01-20		8:15:00	8:45:00	2	01:00:00	01:00:00
10	08-01-20		23:15:00	23:30:00	1	00:15:00	00:15:00
11	12-01-20		23:45:00	0:30:00	3	02:15:00	02:15:00
12
6a

Cell Formulas
Range	Formula
F9:F11	F9	=MOD(D9-C9,1)*E9
G9:G11	G9	=(D9-C9+(C9>D9))*E9