Help with difference between 2 dates including am/pm and check multiple entries to see if they occur at the same date and am/pm

[KHaynes42]

Hi, I'm looking for help calculating differences between dates and then checking multiple lines of data to see if they occur during the same period.

Each line of data represents a repair job, each with a start date and completion date formatted in dd/mm/yyyy hh:mm:ss.
A day consists of 2 periods, one am and one pm session.
Firstly I need to be able to calculate the number of sessions between the 2 dates.
Secondly I then need to be able to check if that date and session appears in another line of data as that will change my ultimate financial calculation.

Example of the sheet attached.
Column C is the first date, Column E is the completed date. My current formula to calculate the difference is in column L but this isn't taking into account the am/pm sessions so in the example of line 5, it's showing 14 sessions but this should be 13 as the job wasn't logged until the pm.

Those that then show as 'fail' in column J need to be checked against all other failing lines to see of they occur at the same time and session.

Any help greatly appreciated

Thank you!

Sample data.xlsx
	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
2															Ratchet - Performance Points
3															0	1.25	1.5
4	Task Ref	Logged Date	Due Fix	Completed Date	SLA Ref	Zone		Performance/Availability	Pass/Fail	Area	Contract Sessions	Zone Category	Performance Points (per Contract Session)	Performance Points (total)	0 to 5	6 to 10	11 upwards
5	2045413	13/12/2023 13:43:00	20/12/2023 13:43:00	04/01/2024 14:03:20	3.7	Circulation		Performance	Fail	Zonal	14	Circulation	25	431.25	125	156.25	150
6	2060944	20/12/2023 15:00:00	08/01/2024 12:00:00	08/01/2024 11:00:00	2.2a	Circulation		Performance	Pass	Whole School	0	Circulation	55	0.00	0	0	0
7	2077056	04/01/2024 10:59:00	12/01/2024 17:00:00	04/01/2024 14:04:44	3.7	Classrooms Practical		Performance	Pass	Zonal	0	Classrooms- Practical	25	0.00	0	0	0
8	2077063	04/01/2024 11:02:00	12/01/2024 17:00:00	04/01/2024 14:05:22	3.7	Admin, Management, Guidance		Performance	Pass	Zonal	0	Admin , Management , Guidance	25	0.00	0	0	0
9	2077121	04/01/2024 11:05:00	12/01/2024 17:00:00	05/01/2024 06:38:15	3.7	Admin, Management, Guidance		Performance	Pass	Zonal	0	Admin , Management , Guidance	25	0.00	0	0	0
10	2077132	04/01/2024 11:45:00	12/01/2024 17:00:00	04/01/2024 14:06:48	3.7	Admin, Management, Guidance		Performance	Pass	Zonal	0	Admin , Management , Guidance	25	0.00	0	0	0
11	2078298	04/01/2024 10:00:00	12/01/2024 17:00:00	18/01/2024 14:03:52	6.7	Toilets,Showers		Performance	Fail	Zonal	16	Toilets , Showers	10	202.50	50	62.5	90
12	2078322	05/01/2024 14:39:00	12/01/2024 17:00:00	17/01/2024 18:24:41	3.7	Admin, Management, Guidance		Performance	Fail	Zonal	14	Admin , Management , Guidance	25	431.25	125	156.25	150
13	2081213	08/01/2024 12:44:00	09/01/2024 10:44:00	09/01/2024 09:24:50	3.5	Circulation		Performance	Pass	Zonal	0	Circulation	55	0.00	0	0	0
14	2082069	09/01/2024 09:03:00	24/01/2024 16:00:00	24/01/2024 10:41:29	3.2	Admin, Management, Guidance		Performance	Pass	Zonal	0	Admin , Management , Guidance	55	0.00	0	0	0
15	2082072	09/01/2024 09:05:00	09/01/2024 13:05:00	09/01/2024 10:06:43	9.2a	Circulation		Performance	Pass	Zonal	0	Circulation	35	0.00	0	0	0
16	2082079	09/01/2024 09:06:00	24/01/2024 16:00:00	18/01/2024 18:06:17	3.2	Classrooms General		Performance	Pass	Zonal	0	Classrooms- General	55	0.00	0	0	0
17	2083117	09/01/2024 15:12:00	10/01/2024 13:12:00	09/01/2024 16:14:19	3.2	Classrooms General		Performance	Pass	Zonal	0	Classrooms- General	55	0.00	0	0	0
18	2083477	09/01/2024 15:23:00	10/01/2024 15:23:00	09/01/2024 15:40:00	9.1	Toilets,Showers		Performance	Pass	Zonal	0	Toilets , Showers	10	0.00	0	0	0
19	2083588	09/01/2024 15:39:00	16/01/2024 15:39:00	15/01/2024 18:32:46	3.7	Classrooms Practical		Performance	Pass	Zonal	0	Classrooms- Practical	25	0.00	0	0	0
20	2084105	09/01/2024 15:00:00	10/01/2024 09:00:00	16/01/2024 13:20:00	7.1	Other External Areas		Performance	Fail	Whole School	12	Other External Areas	100	1425.00	500	625	300
21	2084110	09/01/2024 15:30:00	10/01/2024 09:30:00	15/01/2024 16:30:00	7.1	Circulation		Performance	Fail	Whole School	10	Circulation	100	1125.00	500	625	0
22	2084323	10/01/2024 13:04:00	17/01/2024 13:04:00	16/01/2024 10:52:11	3.7	Classrooms Practical		Performance	Pass	Zonal	0	Classrooms- Practical	25	0.00	0	0	0
23	2084482	10/01/2024 14:55:00	11/01/2024 14:55:00	13/01/2024 15:15:00	9.1	Toilets,Showers		Performance	Fail	Zonal	6	Toilets , Showers	10	62.50	50	12.5	0
24	2085599	11/01/2024 14:09:00	19/02/2024 15:00:00	<no date entered>	2.5	Circulation		Performance	Fail	Zonal	0	Circulation	25	0.00	0	0	0
25	2086078	12/01/2024 08:27:00	12/01/2024 16:27:00	12/01/2024 08:40:00	3.5	Admin, Management, Guidance		Performance	Pass	Zonal	0	Admin , Management , Guidance	55	0.00	0	0	0
26	2087700	15/01/2024 09:33:00	26/01/2024 16:00:00	29/01/2024 15:30:00	2.6	Dining, Kitchen, Sports		Performance	Fail	Zonal	22	Dining , Kitchen , Sports	25	731.25	125	156.25	450
27	2087712	12/01/2024 10:30:00	12/01/2024 12:30:00	12/01/2024 11:15:00	1.1a	Playing fields		Performance	Pass	Whole School	0	Playing Fields	5	0.00	0	0	0
28	2087877	15/01/2024 10:35:00	12/02/2024 16:00:00	<no date entered>	3.1	Stores, plant, janitor etc		Performance	Fail	Whole School	0	Stores,plant,janitor etc.	5	0.00	0	0	0
29	2087910	15/01/2024 10:50:00	15/01/2024 14:50:00	15/01/2024 10:58:33	9.2a	Classrooms General		Performance	Pass	Zonal	0	Classrooms- General	35	0.00	0	0	0
30	2087966	15/01/2024 11:17:00	15/01/2024 15:17:00	15/01/2024 12:26:10	2.2a	Classrooms Practical		Performance	Pass	Whole School	0	Classrooms- Practical	55	0.00	0	0	0
31	2089419	16/01/2024 08:08:00	16/01/2024 08:23:00	16/01/2024 15:19:30	8.2	Classrooms Practical		Performance	Fail	Zonal	2	Classrooms- Practical	10	20.00	20	0	0
32	2089476	16/01/2024 08:47:00	23/01/2024 08:47:00	18/01/2024 10:11:22	6.7	Circulation		Performance	Pass	Zonal	0	Circulation	10	0.00	0	0	0
33	2089480	16/01/2024 08:50:00	16/01/2024 12:50:00	16/01/2024 10:53:43	9.8	Classrooms Practical		Performance	Pass	Zonal	0	Classrooms- Practical	45	0.00	0	0	0
34	2089549	16/01/2024 09:37:00	17/01/2024 07:37:00	16/01/2024 10:54:47	5.2	Other External Areas		Performance	Pass	Zonal	0	Other External Areas	20	0.00	0	0	0
35	2089557	16/01/2024 09:40:00	16/01/2024 13:40:00	16/01/2024 10:17:50	9.8	Circulation		Performance	Pass	Zonal	0	Circulation	45	0.00	0	0	0
36	2089581	15/01/2024 15:00:00	16/01/2024 09:00:00	15/01/2024 16:45:00	6.1	Toilets,Showers		Performance	Pass	Zonal	0	Toilets , Showers	20	0.00	0	0	0
37	2090664	17/01/2024 08:56:00	24/01/2024 08:56:00	18/01/2024 18:07:55	6.7	Classrooms Practical		Performance	Pass	Zonal	0	Classrooms- Practical	10	0.00	0	0	0
38	2090722	17/01/2024 08:51:00	17/01/2024 11:51:00	17/01/2024 12:28:54	3.4	Classrooms General		Performance	Fail	Zonal	2	Classrooms- General	55	110.00	110	0	0
Sample data

Cell Formulas
Range	Formula
I5:I38	I5	=VLOOKUP($F5, Tbl_Specification[[Evo KPI]:[Performance/Availability]], 2, 0)
J5:J38	J5	=IF(E5<=D5,"Pass","Fail")
K5:K38	K5	=VLOOKUP($F5,Tbl_Specification[[Evo KPI]:[Zonal/Whole School]],3,0)
L5:L38	L5	=IF(E5="<no date entered>", 0, IF($J5="Pass",0,(NETWORKDAYS($C5,$E5,Tbl_Holidays[Date])*2)))
M5:M38	M5	=VLOOKUP($G5,Tbl_ZoneConcept[#Data],2,0)
N5:N38	N5	=VLOOKUP($F5, Tbl_Specification[[Evo KPI]:[Performance Points]], 6, 0)
O5:O38	O5	=SUM($P5:$R5)
P5:P38	P5	=IF($I5="Performance", IF($L5>=5, 5 *$N5, $L5*$N5), "")
Q5:Q38	Q5	=IF($I5="Performance", IF($L5<=5,0,IF($L5>=10,5*($N5*$Q$3),IF($L5<10,(($L5-5) *($N5*$Q$3)),0))),"")
R5:R38	R5	=IF($I5="Performance", IF($L5>10,(($L5-10)*($N5*$R$3)),0),"")

Named Ranges
Name	Refers To	Cells
EvoKPI	=Tbl_Specification[Evo KPI]	N5:N38, K5:K38, I5:I38

 

[XLfriend777]

Hello KHaynes42 and welcome to the forum. It is difficult comparing dates in excel because the date displayed by the format it not usually the way that excel is storing the date information. For example excel stores the date in Cell D6 as 45299.5 and Cell E6 as 45299.4583333333.

One approach may be to add columns (even if you hide them later) that make the comparison much easier. You could add a column that just extracts the hour and that is all it displays and then compare it against the hour in another added column. You can do the same thing for days, months, years, minutes, seconds. Hope this makes sense and gets you started.

				Extract for comparison
	=Now()​	=Today()​		Extract Month	Extract Hour
				=Month(C4)	=hour(C4)
Wednesday, March 13, 2024​	3/13/24 11:53​	3/13/24​		3	11	Much easier to compare the extracted values.​
	45364.50​	45364.00​		3	11