Results 1 to 13 of 13
  1. #1
    2 Star Lounger
    Join Date
    Feb 2001
    Posts
    163
    Thanks
    2
    Thanked 0 Times in 0 Posts

    giving name to dates between range (2002 SP3)

    Hi,

    Currently I use the following formula to determine whether an entry in my data extract was opened within the past 12 months ^=IF(DATE(2006,11,1)>R2,"Old","New")

    My extracts consist of 20,000 to 40,000 rows. By inserting a New/Old column and using the formula I can extract information(pivot/filter) based on just those entries that occurred in the previous 12 months.

    I now have the requirement to extract information that is 13 to 24 months old. For this I need to specify if the data in column R is between 2005/11/1 and 2006/10/31 and give it a name eg "prevyear". It doesn't matter what the other data is called, so long as I can identify the data between 13 and 24 months old.

    Can anyone suggest a formula that I could use?

    Thanks

    capri

  2. #2
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Perth, Western Australia, Australia
    Posts
    190
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: giving name to dates between range (2002 SP3)

    have you tried adding an additional column to the pivot data the will return TRUE/FALSE if a date is between two dates.
    The dates could be in separate cells (max date, min date) and then data will then be able to be 'filtered' simply by changing these key dates.

  3. #3
    5 Star Lounger
    Join Date
    Aug 2004
    Location
    Connecticut, USA
    Posts
    816
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: giving name to dates between range (2002 SP3)

    The following will mark any date before 11/1/05 as Old, dates between 11/1/05 and 10/31/06 as PrevYear and anything after 10/31/06 as this year.

    =IF(AND(R2>=DATE(2005,11,1),R2<=DATE(2006,10,31)), "PrevYear",IF(R2>=DATE(2006,11,1),"this year","Old"))

  4. #4
    2 Star Lounger
    Join Date
    Feb 2001
    Posts
    163
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Re: giving name to dates between range (2002 SP3)

    Thanks so much Mbarron

    The formula is exactly what I need. I am working on rolling 12 months data, so the dates need to change each month, and this will allow me to easily access the information i need.

  5. #5
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: giving name to dates between range (2002 SP3)

    If the dates are gooing to change instead of changing all the formulas, why not put the start and end dates in a cell and have all the formulas compare to these cells, then you only have to edit the start and end dates and not all the formulas.

    Better still, in addition you could calculate the start and end dates using the current year (eg: [=year(today()) -2] and current month [=month(today())])

    Also since you do the same calculations within 20,000 cells using an intermediate calculation would prevent it from having to be calculated 20,000 times...

    Steve

  6. #6
    3 Star Lounger
    Join Date
    Feb 2003
    Location
    Runcorn, Cheshire, United Kingdom
    Posts
    372
    Thanks
    0
    Thanked 2 Times in 2 Posts

    Re: giving name to dates between range (2002 SP3)

    Could you use the mostly undocumented DATEDIF function ?

    =IF(DATEDIF(Date1,TODAY(),"m")<12,"New",IF(DATEDIF (Date1,TODAY(),"m")<24,"PrevYear","Old"))

    Explanation of DateDif
    DATEDIF(Date1, Date2, "m") returns the number of complete months between Date1 and Date 2

    other options are
    "d" - complete days
    "y" - complete years
    "ym" - complete months excluding years (returns 0 - 11)
    "yd" - days excluding years ( 0 - 364)
    "md" days excluding years and months (0 - 30)

    Or you could simply use the "y" argument to tell you whether the extract date was '0', '1' or ''2+' years ago.

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: giving name to dates between range (2002 SP3)

    For others reading this: Chip Pearson has a useful page about the DATEDIF function: DATEDIF Worksheet Function.

    (DATEDIF is available in all recent versions of Excel, but it has only been documented in the help files in Excel 2000 for unknown reasons)

  8. #8
    3 Star Lounger
    Join Date
    Feb 2003
    Location
    Runcorn, Cheshire, United Kingdom
    Posts
    372
    Thanks
    0
    Thanked 2 Times in 2 Posts

    Re: giving name to dates between range (2002 SP3)

    I suspect that the way that DateDif works has varied over different releases, which is probably why it is not supported.
    For example
    The way that "md" works in Excel 97 is not the same as Chip describes.
    Whereas Chip states that "md" gives a result of '28' for 1 Feb 07 and 1 Mar 09, my version of Excel 97 gives a result of '0'

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: giving name to dates between range (2002 SP3)

    Excel 2002 (aka XP) returns 0 too, and that appears to be the correct result to me - it might be a mistake by Chip (although that's rare).

  10. #10
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: giving name to dates between range (2002 SP3)

    AFAIK, the DATEDIF worksheet function has not changed from release to release, other than to disappear from the Help files. I think it's just a typo on Chip's site and he meant "yd" not "md".
    Regards,
    Rory

    Microsoft MVP - Excel

  11. #11
    3 Star Lounger
    Join Date
    Feb 2003
    Location
    Runcorn, Cheshire, United Kingdom
    Posts
    372
    Thanks
    0
    Thanked 2 Times in 2 Posts

    Re: giving name to dates between range (2002 SP3)

    In which case it's worth clarifying that the way that "md" works is as follows
    If Date 1 has day_1 of month_1 of year_1
    and Date 2 has day_2 of month_2 of year_2

    To calculate md value do the following <UL><LI>If day_1 is less than day_2 then md value is day_2 minus day_1
    <LI>If day_1 is greater than day_2, subtract 1 from month_2 and count forward from day_1, through the last day of the month (28, 29, 30 or 31 as appropriate for month and year), until you reach day_2
    <LI>If day_1 = day_2, return zero[/list]<table border=1><td>Date_1</td><td>Date_2</td><td>md value</td><td>comments</td><td>01/03/2006</td><td>15/02/2007</td><td>14</td><td>simple subtraction</td><td>15/03/2006</td><td>01/02/2007</td><td>17</td><td>January preceeds February and has 31 days</td><td>15/03/2006</td><td>01/03/2007</td><td>14</td><td>February preceeds March and has 28 days in 2007</td><td>15/03/2006</td><td>01/03/2008</td><td>15</td><td>February preceeds March and has 29 days in 2008</td></table>Whether intervals are based on 365 days or 366 days depends on the value of year_2

    I've attached a spreadsheet that should point up if the function varies in its results. If anyone sees any blue cells, let us know which version you're using.
    Attached Files Attached Files

  12. #12
    2 Star Lounger
    Join Date
    Feb 2001
    Posts
    163
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Thanks

    Thanks for the various suggestions. It's always nice to have more than one method. I've certainly learned a few new things, about the DATEIF function and using the YEAR/TODAY functions in formulas.

    The formula that Mbarron gave which starts =IF(AND has me wondering how you would know to use that combination in a formula. I understand creating IF statements and nesting them, but don't understand that combination and how it works. Could someone suggest where I might read more on that combination or other ways to make formulas more flexible by combining functions in a similar manner.

    capri

  13. #13
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Thanks

    There's nothing magic about the combination IF(AND(...

    AND can be used to combine two or more conditions. AND(a,b,...) is TRUE if all of the conditions a, b, etc. are TRUE, and FALSE if at least one of the conditions a, b, etc. is FALSE.

    Similarly, OR(a,b,...) is TRUE if at least one of the conditions a, b, etc. is TRUE, and FALSE if all of the conditions a, b, etc. are FALSE.

    To learn more about worksheet functions, take any good Excel book, for example Book: Excel 2003 Formulas by John Walkenbach.

Posting Permissions

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