Dates and periods

[Coliber]

Hi,

I have a "small" problem with dates-related formula.
SCENARIO
_Period_Range_Worksheet_
Column A - name of a period ie. Period01 2005, Period02 2005 and so on
for the next 50 years (periods are 28 days, rather than monthly, so
there are 13 periods in any given fiscal year)

Column B - start dates for each particular period, corresponding to
column A

Column C - end dates for each particular period, corresponding to
column A and B

Now the problem:
_Calculation_worksheet_
In a separate worksheet I want to be able to enter ANY date (within 50
years range) in column A, and get a proper period and year match from
Period Range worksheet ie. enter January 20, 2007 and get Period 01
2007 in column B.

I tried Index, Match combination - did not work.

The main problem is the the date typed most often falls within the
range of start and end dates for any particualr period, rather than be
an exact match to the start or end date for any particular period.

Any thoughts from you guys ????

Thanks in advance.

 

[John Michl]

Any chance you could put your list of periods to the right of the date
ranges? If column D equaled column A, then your lookup formula would
be

= VLOOKUP(A1,Period_Range_Worksheet!B11000,3)

Question: Thirteen 28-day periods equals 364 days. What do you do with
extra day (or extra two days during leap year)?

- John

 

[Coliber]

John,

the problem with vlookup is that the sample date entered will most
likely
NOT be an exact match for any start or end dates of period dates - it
will 99% of time fall between any given start and end date, thus
vlookup will not be able to match the range dates and corresponding
period with sample date.

As to the 1 missing day in a year, the periods exact length is
calculated by the formula
"=IF(AND(A2="P13",DAY(C2+27)<=27,MONTH(C2+27)=12),C2+34,C2+27)"

Thanks for your input and very quick reply.

Coliber

 

[John Michl]

Coliber, it doesn't need to be an exact match as long as you don't have
the FALSE parameter at the end of the formula. Assuming the dates are
in order, VLOOKUP will find the closest value without going over. Try
it. You'll see that it works.

- John

 

[Sandy Mann]

Coliber

With the first of your start dates in B2 on a sheet named "Data"

=INT((C5-Data!B2)/28)*28+Data!B2

will return the start date of the period of a date in C5 of your 'other'
sheet. You can then use this date in your VLOOKUP

--
HTH

Sandy
(e-mail address removed)
(e-mail address removed) with @tiscali.co.uk

 

[Coliber]

John,

You are right. It worked.

Thanks again,

Coliber

 

[Coliber]

Sandy Mann,

thanks for your solution.

It is good as well.

Colibe