Finding date when the sum in a range equals the hours planned

**TryToUnderstandExcel** · 09-30-2014, 06:45 AM

Hello,

I am having a problem for my excel planning tool.

I wish to enter in different columns (dates) in 1 row an amount of planned ours spent on a specific task (task = row)

In a cell I wish to enter the hours used and next to it the date on which I should be at the moment

for exemple:

Date: 01-10 | 02-10 | 03-10 | 04-10
Hours: 20 | 20 | 20 | 20

Used hours| 45 (fill in) | Date| 03-10 (sum of hours is 40 at 02-10, which is to small, at 03-10 the sum is 60, which is bigger (or the same) as used hours)

Attached is a file to see this situation.

Does anyone know a formula to find the date based on used hours?

**Tony Valko** · 09-30-2014, 09:39 AM

I didn't download your file (too big!).

Try something like this...

Data Range

	A	B	C	D	E
1	Date	10/1/2014	10/2/2014	10/3/2014	10/4/2014
2	Hours	20	20	20	20
3
4	Lookup	Date
5	45	10/3/2014

This array formula** entered in B5:

=INDEX(B1:E1,MATCH(TRUE,SUBTOTAL(9,OFFSET(B2,,,,COLUMN(B2:E2)-COLUMN(B2)+1))>=A5,0))

Format as Date

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.