Return result within range of times using NOW

[genichigo]

Hi all

stuck on how to get this to work

In cells A1 onwards i have this:

Time

1 Start	Time 1 End	Time 2 Start	Time 2 End	Time 3 Start	Time 3 End
10:00	10:15	12:30	13:00	15:00	15:15

<colgroup><col><col><col span="2"><col><col></colgroup><tbody>
</tbody>

In G 2 (after Time 3 end) i want to do a search and return the value of time related to the task, ie if it is 10:14 it will return the A1 result "Time 1 Start", but after the end point (10:15) it will not return anything until "Time 2 Start" so will only return the start time value and not end times? if that makes sense?

 

[Aladin Akyurek]

Row\Col	A​	B​	C​	D​	E​	F​	G​
1​	Time 1 Start	Time 1 End	Time 2 Start	Time 2 End	Time 3 Start	Time 3 End	10:14​
2​	10:00​	10:15​	12:30​	13:00​	15:00​	15:15​	Time 1 Start

In G2 control+shift+enter, not just enter:

=INDEX($A$1:$E$1,MATCH(1,IF($A$2:$E$2>=G1,IF(G1<=$B$2:$F$2,1)),0)-1)

 

[genichigo]

Thanks, when i set up the above it gets stuch at Time 1 End and doesn't update after this?

 

[Snakehips]

I may well have misunderstood but I was thinking you might be trying to get a cumulative time to date for the three blocks of time but use NOW if there is an incomplete record because of a missing end time?

Anyhow, here is my suggestion based on that assumption.

Excel Workbook
	A	B	C	D	E	F	G	H
1	Start1	End1	Start2	End2	Start3	End3	Total
2	10:00	10:20	11:12	11:21			00:29
3	10:00	10:20	11:12				03:27
4	10:00	10:20	11:12	14:40			03:48
5	10:00	10:20	11:12	14:40	15:00	15:25	04:13
Sheet1

Hope that helps.

 

[Aladin Akyurek]

Thanks, when i set up the above it gets stuch at Time 1 End and doesn't update after this?

What do you mean by update? The formula will report the appropriate start time header for the time value you enter in G1.