Return result within range of times using NOW

genichigo

New Member
Joined
Mar 17, 2015
Messages
12
Hi all

stuck on how to get this to work

In cells A1 onwards i have this:

Time
1 StartTime 1 EndTime 2 StartTime 2 EndTime 3 StartTime 3 End
10:0010:1512:3013:0015:0015: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?
 

Some videos you may like

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201
Row\Col
A​
B​
C​
D​
E​
F​
G​
1​
Time 1 StartTime 1 EndTime 2 StartTime 2 EndTime 3 StartTime 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

New Member
Joined
Mar 17, 2015
Messages
12
Thanks, when i set up the above it gets stuch at Time 1 End and doesn't update after this?
 

Snakehips

Well-known Member
Joined
May 17, 2009
Messages
5,069
Office Version
  1. 2013
Platform
  1. Windows
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
ABCDEFGH
1Start1End1Start2End2Start3End3Total
210:0010:2011:1211:2100:29
310:0010:2011:1203:27
410:0010:2011:1214:4003:48
510:0010:2011:1214:4015:0015:2504:13
Sheet1


Hope that helps.
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,122,418
Messages
5,596,028
Members
414,039
Latest member
southike

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top