software

Hi,

I have a quot;smallquot; 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. --
Coliber
------------------------------------------------------------------------
Coliber's Profile: www.excelforum.com/member.php...oamp;userid=30864
View this thread: www.excelforum.com/showthread...hreadid=505400Any 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
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
quot;=IF(AND(A2=quot;P13quot;,DAY(C2 27)lt;=27,MONTH(C2 27)=12), C2 34,C2 27)quot;

Thanks for your input and very quick reply.

Coliber --
Coliber
------------------------------------------------------------------------
Coliber's Profile: www.excelforum.com/member.php...oamp;userid=30864
View this thread: www.excelforum.com/showthread...hreadid=505400Coliber, 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.

- JohnColiber

With the first of your start dates in B2 on a sheet named quot;Dataquot;

=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
with @tiscali.co.ukquot;Coliberquot; gt; wrote in
message ...
gt;
gt; Hi,
gt;
gt; I have a quot;smallquot; problem with dates-related formula.
gt; SCENARIO
gt; _Period_Range_Worksheet_
gt; Column A - name of a period ie. Period01 2005, Period02 2005 and so on
gt; for the next 50 years (periods are 28 days, rather than monthly, so
gt; there are 13 periods in any given fiscal year)
gt;
gt; Column B - start dates for each particular period, corresponding to
gt; column A
gt;
gt; Column C - end dates for each particular period, corresponding to
gt; column A and B
gt;
gt; Now the problem:
gt; _Calculation_worksheet_
gt; In a separate worksheet I want to be able to enter ANY date (within 50
gt; years range) in column A, and get a proper period and year match from
gt; Period Range worksheet ie. enter January 20, 2007 and get Period 01
gt; 2007 in column B.
gt;
gt; I tried Index, Match combination - did not work.
gt;
gt; The main problem is the the date typed most often falls within the
gt; range of start and end dates for any particualr period, rather than be
gt; an exact match to the start or end date for any particular period.
gt;
gt; Any thoughts from you guys ????
gt;
gt; Thanks in advance.
gt;
gt;
gt; --
gt; Coliber
gt; ------------------------------------------------------------------------
gt; Coliber's Profile:
gt; www.excelforum.com/member.php...oamp;userid=30864
gt; View this thread: www.excelforum.com/showthread...hreadid=505400
gt;

John,

You are right. It worked.

Thanks again,

Coliber John Michl Wrote:
gt; Coliber, it doesn't need to be an exact match as long as you don't have
gt; the FALSE parameter at the end of the formula. Assuming the dates are
gt; in order, VLOOKUP will find the closest value without going over. Try
gt; it. You'll see that it works.
gt;
gt; - John--
Coliber
------------------------------------------------------------------------
Coliber's Profile: www.excelforum.com/member.php...oamp;userid=30864
View this thread: www.excelforum.com/showthread...hreadid=505400
Sandy Mann,

thanks for your solution.

It is good as well.

Coliber--
Coliber
------------------------------------------------------------------------
Coliber's Profile: www.excelforum.com/member.php...oamp;userid=30864
View this thread: www.excelforum.com/showthread...hreadid=505400