+ Reply to Thread
Results 1 to 8 of 8

Dates and periods

  1. #1
    Registered User
    Join Date
    01-26-2006
    Posts
    4

    Dates and periods

    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.

  2. #2
    John Michl
    Guest

    Re: Dates and periods

    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!B1:D1000,3)

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

    - John


  3. #3
    John Michl
    Guest

    Re: Dates and periods

    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!B1:D1000,3)

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

    - John


  4. #4
    Registered User
    Join Date
    01-26-2006
    Posts
    4
    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. - I am wrong here - look below

    I just tested vlookup and it will work, but the condition has to be defined as FALSE.

    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
    Last edited by Coliber; 01-26-2006 at 02:28 PM.

  5. #5
    John Michl
    Guest

    Re: Dates and periods

    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


  6. #6
    Sandy Mann
    Guest

    Re: Dates and periods

    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
    [email protected]
    [email protected] with @tiscali.co.uk


    "Coliber" <[email protected]> wrote in
    message news:[email protected]...
    >
    > 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.
    >
    >
    > --
    > Coliber
    > ------------------------------------------------------------------------
    > Coliber's Profile:
    > http://www.excelforum.com/member.php...o&userid=30864
    > View this thread: http://www.excelforum.com/showthread...hreadid=505400
    >




  7. #7
    Registered User
    Join Date
    01-26-2006
    Posts
    4
    John,

    You are right. It worked.

    Thanks again,

    Coliber


    Quote Originally Posted by 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

  8. #8
    Registered User
    Join Date
    01-26-2006
    Posts
    4
    Sandy Mann,

    thanks for your solution.

    It is good as well.

    Coliber

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1