Need to find time difference between most recent time listed in a column

[knoharra]

I am trying to calculate the time difference between 2 cells in time column A, but only if the time slot cell in Column B is used. See screenshot below of Columns A&B, assuming the top left is cell A1. For example, B4 (13:30) correctly shows 13h and 30 min between cells a4 & a3 times. However, in cell b11 (1:00) I want to calculate 4:00 (4h between 12pm in a11 and 8am in a4). How can I do this? Currently I am just using an if function referring to another cell to return a value or "-". If needed I am ok with changing the "-" to "#N/A" and I am able to do this, I just thought the "-" was cleaner looking. Thank you.

 

[knoharra]

Ok so I'm realizing this isn't always working as intended. I've attached some minisheets to help. My plan is to look

Schedule Makeup New.xlsm
	A	B	C
1	Time/Field	Field 1	Field 2
2	9/12/25 6:00 PM	U08 Boys Blue, A vs C	0
3	9/12/25 6:15 PM	0	0
4	9/12/25 6:30 PM	0	U08 Boys Blue, B vs D
5	9/13/25 8:00 AM	U08 Boys Blue, D vs A	U08 Boys Blue, B vs C
6	9/13/25 11:00 AM	U08 Boys Blue, A vs B	0
7	9/14/25 8:00 AM	0	U08 Boys Blue, C vs D
8	9/14/25 10:00 AM	U08 Boys Blue, 1st vs 2nd	0
MasterChart

Cell Formulas
Range	Formula
B1:W1	B1	=TRANSPOSE(Field[Field])
A2:A8	A2	=SORT(Time[Combined])
B2:C8	B2	=IFERROR(TEXTJOIN({", "," vs "},TRUE,CHOOSECOLS(FILTER(MasterSchedule!$A$6:$E$505,(MasterSchedule!$D$6:$D$505=B$1)*(MasterSchedule!$E$6:$E$505=$A2)),1,2,3)),0)
Dynamic array formulas.

Schedule Makeup New.xlsm
	A	B	C
53	Time/Field	Field 1	Field 2
54	9/12/25 6:00 PM	#VALUE!	-
55	9/12/25 6:15 PM	-	-
56	9/12/25 6:30 PM	-	0:15
57	9/13/25 8:00 AM	13:30	13:30
58	9/13/25 11:00 AM	3:00	-
59	9/14/25 8:00 AM	-	0:00
60	9/14/25 10:00 AM	23:00	-
MasterChart

Cell Formulas
Range	Formula
A53	A53	=A1
B53:C53,A54:A60	B53	=IF(ISBLANK(B1),"",B1)
B54:C60	B54	=IF(B2=0,"-",$A2-IFERROR(LOOKUP(9^9,B$53:B53,$A$53:$A53),$A53))

Named Ranges
Name	Refers To	Cells
_FilterDatabase	=MasterChart!$A$1:$W$50	A53

Schedule Makeup New.xlsm
	A	B	C	D
1	Field	Size	MinBetween (Prelim)	MinBetween (Final)
2	Field 1	7v7	65	75
3	Field 2	11v11	75	95
Fields

Cell Formulas
Range	Formula
C2:C3	C2	=IFS(OR(B2="7v7",B2="9v9"),65,B2="11v11",75)
D2:D3	D2	=IFS(B2="7v7",75,B2="9v9",85,B2="11v11",95)

Cells with Data Validation
Cell	Allow	Criteria
B2:B3	List	7v7,9v9,11v11

 

[Micron]

Excel formulas are not my thing so I can't help with those. I lean towards code.

 

[Peter_SSs]

Sorry, I am not following well enough.

 

[knoharra]

Sorry, I am not following well enough.

Ultimately I'm trying to report the start time between games. However, since it's a master range of all possible start times, I can't simply subtract the time from the previous time block as there may not have been a game on that field during that time. I need to subtract from the last start time on the field in that column. Then conditionally format any results that are less than a referenced amount of time based on the game length. I think I've got it working now, but haven't confirmed with a fully completed schedule. I'll try likely late August 2025. My formulas follow...

B54:
=IFERROR(IF(B2="-","-",$A2-TAKE(FILTER($A$1:$A1,(B$1:B1<>"-")),-1)),"1st game on "&B$53)


Conditional formatting in B54:
=(B54+0.0001)<IF(OR(ISNUMBER(FIND("1st",B1)),ISNUMBER(FIND("2nd",B1)),ISNUMBER(FIND("5th",B1)),ISNUMBER(FIND("Div",B1)))=TRUE,INDEX(Fields!$A$2:$D$50,MATCH(B$53,Fields!$A$2:$A$50),4)/1440,INDEX(Fields!$A$2:$D$50,MATCH(B$53,Fields!$A$2:$A$50),3)/1440)


Note in conditional I had to add a few seconds to my result b/c in a few instances rounding seemed to make it falsely format cells right at the acceptable time limit.

 

[Peter_SSs]

I think I've got it working now, ...

Good news.