Linking Dates to a Date Range

[Jamie2002]

Please can someone help using Excel or Access

I have a list of data ie;


Name StartDate EndDate Type
Program 1 14/12/2003 10:00:00 14/12/2003 11:15:00 A
Program 2 14/12/2003 13:15:00 14/12/2003 13:30:00 B

Then I have a list of transactions ie;

Trans No Date Amount
00001 14/12/2003 09:31:48 10.00
00002 14/12/2003 10:01:01 5.00
00003 14/12/2003 10:01:01 5.00
00004 14/12/2003 11:14:59 5.00
00005 14/12/2003 13:14:59 5.00
00006 14/12/2003 13:28:00 5.00

How can I lookup in Excel or Access what Type would be allocated to each transaction ie;

Trans 00001 would be X(or no type), 00002 type A, 00003 type A, 00004 type A, 00005 type X, 0006 type B

I've tried adding date values and then looking up but I seem to be getting the value preious when using the Vlookup function.......

I'm at the end of another 11 hour day in work and just cant think anymore.......please help.

Thank you.




Thanks

Jamie

 

[SkipVought]

Jamie,

Name you ranges using Insert/Name/Create Create name in top

=IF(AND(Date>=OFFSET(StartDate,0,0,1,1),Date<=OFFSET(EndDate,0,0,1,1)),OFFSET(Type,0,0,1,1),IF(AND(Date>=OFFSET(StartDate,1,0,1,1),Date<=OFFSET(EndDate,1,0,1,1)),OFFSET(Type,1,0,1,1),&quot;X&quot;))

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

Skip,

http://www.TheOfficeExperts.com

 

[bluedragon2]

You can do a check with a vlookup:

in cells a1:d3 is:

Name StartDate EndDate Type
Program 1 14/12/2003 10:00:00 14/12/2003 11:15:00 A
Program 2 14/12/2003 13:15:00 14/12/2003 13:30:00 B

your lookup date is in say cell F2 then:

=IF(F2<VLOOKUP(F2,B13,2),VLOOKUP(F2,B13,3),&quot;X&quot



[Blue]Blue[/Blue] [Dragon]

If I wasn't Blue, I would just be a Dragon...

 

[bluedragon2]

Also, if you copy and paste the formula, remember to make absolute references you do not want to change (i.e. $B$2:$D$3)



[Blue]Blue[/Blue] [Dragon]

If I wasn't Blue, I would just be a Dragon...