Not sure if Vlookup is the right choice

[KFrank]

Hi all,

I am a bit confused on this one.

I work shifts of 12.3 hours. I have a certain amount of hours to complete before I can go on holiday. I would like cell D2 to tell me on what day I finish my hours.

As you can see in cell C324, I have to work 2.7 hours and then my hours for the season will be finished.

I need the formula in D2 to say "Find the cell in column C that is less than 12.3 but more than zero (as the cells below C324 are blank) and display the information in the cell in column D next to that cell.

So D2 should display cell D324 or 06/06/2024.

Can anybody help?

EDIT:
My apologies, I have made an error in my calculations.

Cell D2 actually needs to show D325 as that is the first cell that has zero hours remaining and would be the day I have finished working. Cell D324 shows that I still have 2.7 hours to work the next day.

 

[Jamie McMillan]

Hello KFrank,

An easy formula, in cell D2, to display the first blank cell is:-

="D"&COUNT(C8:C365)+1

It simply, puts in the letter D and then counts the number of Data in column C (from row 8 to 365) and adds one to it - the first empty cell.

Jamie

 

[Peter_SSs]

Try this formula structure. Notice the ranges are offset by 1 row.

22 05 07.xlsm
	C	D
1
2		16/05/2022
3
4
5
6
7
8	88.8	1/05/2022
9	88.8	2/05/2022
10	88.8	3/05/2022
11	88.8	4/05/2022
12	88.8	5/05/2022
13	88.8	6/05/2022
14	88.8	7/05/2022
15	88.8	8/05/2022
16	88.8	9/05/2022
17	88.8	10/05/2022
18	88.8	11/05/2022
19	39.6	12/05/2022
20	27.3	13/05/2022
21	15	14/05/2022
22	2.7	15/05/2022
23		16/05/2022
24		17/05/2022
25		18/05/2022
26		19/05/2022
Date

Cell Formulas
Range	Formula
D2	D2	=LOOKUP(9^9,C8:C25,D9:D26)