I have a list of meeting days/times and separate list of tasks with end
date/times. Can someone help me to produce the formula that will match
the task end date/time with the next meeting after the task is due to
complete?


--
mc32
------------------------------------------------------------------------
mc32's Profile: http://www.excelforum.com/member.php...o&userid=31272
View this thread: http://www.excelforum.com/showthread...hreadid=509432

Is the date/time in the same cell, or different cells?

"mc32" wrote:


I have a list of meeting days/times and separate list of tasks with end
date/times. Can someone help me to produce the formula that will match
the task end date/time with the next meeting after the task is due to
complete?


--
mc32
------------------------------------------------------------------------
mc32's Profile: http://www.excelforum.com/member.php...o&userid=31272
View this thread: http://www.excelforum.com/showthread...hreadid=509432

I have it both ways


--
mc32
------------------------------------------------------------------------
mc32's Profile: http://www.excelforum.com/member.php...o&userid=31272
View this thread: http://www.excelforum.com/showthread...hreadid=509432

Here's a proposal:

B C D E
1 Meetings task prior
2 meetings key date time to meeting
3 01.02.2006 14:00 01.02.2006 14:00 too early
4 12.04.2006 10:00 12.04.2006 10:00 10.04.2006 11:00
5 08.05.2006 11:30 08.05.2006 11:30 10.04.2006 11:00
6
7
8
9 tasks meeting prior
10 tasks key date time to tasks
11 13.02.2006 13:00 13.02.2006 13:00 01.02.2006 14:00
12 10.04.2006 11:00 10.04.2006 11:00 01.02.2006 14:00
13 09.05.2006 11:30 09.05.2006 11:30 08.05.2006 11:30

Formula in B4: =SUM(C4:D4) format as dd.mm.yyyy hh:mm
Copy down as required

Formula in B12: =SUM(C12:D12) format as dd.mm.yyyy hh:mm
Copy down as required

Formula in E4: =IF(ISNA(VLOOKUP(B4;$B$12:$D$14;1;TRUE));"too
early";VLOOKUP(B4;$B$12:$D$14;1;TRUE))
format as dd.mm.yyyy hh:mm
Copy down as required

Formula in E12: =IF(ISNA(VLOOKUP(B12;$B$4:$D$6;1));"too
early";VLOOKUP(B12;$B$4:$D$6;1))
format as dd.mm.yyyy hh:mm
Copy down as required

Columns C and D is your Input.

Hans

Sorry,

B3:D5 and B11:D13 need to be sorted in ascending order of B

Hans