giving name to dates between range (2002 SP3)

Topic

New Reply

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

Reply | Quote

Replies

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.

Reply | Quote

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,11,1),”this year”,”Old”))

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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)

Reply | Quote

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’

Reply | Quote

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).

Reply | Quote

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”.

Reply | Quote

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

• If day_1 is less than day_2 then md value is day_2 minus day_1
• 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
• If day_1 = day_2, return zero
  [/list]

  Date_1	Date_2	md value	comments
  01/03/2006	15/02/2007	14	simple subtraction
  15/03/2006	01/02/2007	17	January preceeds February and has 31 days
  15/03/2006	01/03/2007	14	February preceeds March and has 28 days in 2007
  15/03/2006	01/03/2008	15	February preceeds March and has 29 days in 2008

  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.

Reply | Quote

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

Reply | Quote

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.

Reply | Quote