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.