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.

 

[Micron]

If your dates are formatted as dates and the B column is formatted as Time then there shouldn't be an issue except

but only if the time slot cell in Column B is used

you can't have data entered as a value and also a formula in the same cell. Besides, every row in colB that you show is used - you have dashes in there.

 

[knoharra]

It works if I manually select each cell, but I'm hoping to use something that says to only calculate the difference in column a rows if there is a non " - " value in a cell reference outside of my screenshot (eg C1).

 

[Micron]

try this using your cell references
=IF(Y2<>"-",W3-W2,"")

I used W for your A column and Y for your C column

 

[knoharra]

try this using your cell references
=IF(Y2<>"-",W3-W2,"")

I used W for your A column and Y for your C column

I'm probably not explaining well. This is similar to what I have, but it always calculates the difference in the most recent previous time. If the most recent time is not used, instead of w3-w2, I instead want to calculate for example w3-w1.

For clarity, imagine game time slots a,b,& c on a field. Field 1 has a game during time slots a&c but field 2 in another column has games during time slots b&c. I want a common formula that gives time c-a for Field 1 but c-b for Field 2. Another section of my worksheet tells excel if there is a game during the time slots in column a. Does this make more sense?

 

[Micron]

I'm probably not explaining well

No argument there. I suggest you try posting sample data as well as expected results. xl2BB is the best method; copy/paste from sheet not as good but is still better than pics. With pics you force people to type out data to try things, and some won't, so you lose potential participants.

 

[Peter_SSs]

Welcome to the MrExcel board!

As suggested by @Micron please consider XL2BB for your sample data to make it easier for people to help you.
(If you have trouble with XL2BB, review the "XL2BB Icons greyed out" link in the 'Known XL2BB issues' section near the top of the XL2BB Instructions page linked above.)

See if this is the sort of thing you are after.

24 12 07.xlsm
	A	B	C
1	Date/Time	Time Diff	Ref
2	12/09/2025 6:00 PM	-	-
3	12/09/2025 6:15 PM	-	-
4	12/09/2025 6:30 PM	0:15
5	13/09/2025 8:00 AM	13:30
6	13/09/2025 8:15 AM	-	-
7	13/09/2025 9:00 AM	-	-
8	13/09/2025 9:30 AM	-	-
9	13/09/2025 10:00 AM	-	-
10	13/09/2025 10:45 AM	-	-
11	13/09/2025 11:00 AM	-	-
12	13/09/2025 12:00 PM	4:00
13	13/09/2025 1:00 PM	-	-
Time Diff

Cell Formulas
Range	Formula
B2:B13	B2	=IF(C2="-",C2,A2-IFNA(LOOKUP(9^9,B$1:B1,A$1:A1),A1))

 

[knoharra]

Thanks guys. The IFNA & lookup function works as intended. In the future I'll try to include an XL2BB file.

 

[Peter_SSs]

You're welcome. Glad we could help. Thanks for the follow-up.

 

[knoharra]

Ok so I'm realizing this isn't always working as intended. I've attached some minisheets to help. I have made a sports tournament master schedule (1st minisheet, a range rather than table since the times and fields are an array that could change each tournament). Games are identified as anything that begins with U*, and anything with "1st", "2nd", "5th", or "Div" in the white cells is a Championship Game with longer time needed between games, while any game without this is preliminary. FYI I'm currently using "0" in this chart as I was trying to find a way using non-zero numbers, but ideally I'd like to keep non-games as "-" if possible.

My goal is to ensure adequate time between games. I'm doing this in the 2nd minisheet where fields/times replicate those in the 1st. In B54, ideally I'd like it to state "First Game" since there are no prior games on Field 1, but not required. I do need A55 and all cells under this in column A to give me the times between games. So B57 should give h:mm as 14:00 for 14 hours between A57 & A54. Currently it's giving 13:30 since only goes back to previous time slot which would work if I'm only dealing with 1 field, but I'm dealing with several. B58 calculates ok since previous game is previous time slot, and B60 is ok I don't understand formula well enough to understand why. But in column C (Field 2) I'd like to copy formula over so that C56 says "First Game", C57 gives 13:30, and C59 gives 24:00.

Once all correct, I'd like a conditional formatting formula that will highlight red anything that is less than the minimum time between games, referenced using the "Field" table in a sheet called "Fields", segregating times based on whether it's preliminary or final/championship game as outlined in my 1st paragraph and times referenced in the "Field" table. FYI I removed my conditional formatting I had temporarily to highlight the cells that aren't doing what I wanted.

Thanks for any help you can offer.

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