Previous date in List Question

  • Thread starter Thread starter SPR
  • Start date Start date
S

SPR

Hello,


I have a vertical list of dates in a column, that are
designed not to have weekends or holidays in them. They
are also in order.


In cell A1 I have a date. What formula can I put in cell
B2 that will lookup the column of dates, and return the
day in the list before the date mentioned in A1?


Its some combination of offset and vlookup I believe....


Cheers
 
Here's one way:

=INDEX(DateList,MATCH(A1,DateList,0)-1)

Format B2 as date.

--
Best Regards
Leo Heuser
MVP Excel

Followup to newsgroup only please.
 
Morning,


Thanks for the reply, however I think I mis-worded what I
was looking for.


What I should have said is that the date in A1 should be
used as a reference point with rules as follows:


If the date falls on a weekend or holiday (ie not on the
list of vertical dates), choose to either go forward/down
to the next day in the list - if it is still the same
month, or....... go backwards a day


A sticking point with the Match/Index function is that if
the date in A1 is not in the list, ir returns the N/A
error.


Cheers
 
SPR said:
What I should have said is that the date in A1 should be
used as a reference point with rules as follows:

If the date falls on a weekend or holiday (ie not on the
list of vertical dates), choose to either go forward/down
to the next day in the list - if it is still the same
month, or....... go backwards a day
Click to expand...
....

If your dates are sorted and there are no duplicates, then

MATCH(A1,ListOfDates)

will give the index of the last weekday before A1 if A1 isn't in the list.
Add one to it to get the index of the next weekday in the list. Then, given
your specs, try

=INDEX(ListOfDates,MATCH(A1,ListOfDates)+
(MONTH(INDEX(ListOfDates,MATCH(A1,ListOfDates)))=
MONTH(INDEX(ListOfDates,MATCH(A1,ListOfDates)+1))))
 
Back
Top