Match + Index(?) Question

[Guest]

I have a worksheet as follows:
March 30, 2005 Mar
Jan 1
Feb 2
Mar 3
Apr 4
May 5
Jun 6
Jul 7
Aug 8
Sep 9
Oct 10
Nov 11
Dec 12

where A1 is =today() and delivers the current date
where B1 = text(A1,"mmm") which extracts "MAR" from the date

in another cell, say D1, I have a formula =MATCH(B2,A2:A12,0) to deliver the
month "number" to use elsewhere in a formula (an annualization formula where
the YTD value is divided by the month number, then multiplied by 12). In
this case, it delivers 3. Now the puzzle: I only want the month number to
increase, say from 2 to 3, only after the 30th of the month. Example: March
29 delivers "2", and March 30 delivers "3". I tried using text to extract
the "dd" but it failed. Any ideas?

Kem

 

[Dave O]

It looks like you've created the Jan - Dec table in cells A2:A13 to
provide a "lookup" capability that returns the month number. If that's
the case, then you've duplicated an existing Excel function: the
MONTH() function returns the integer month number of a date. Another
function that may help resolve your problem is the DAY() function,
which returns the day specified in a date.

To solve your problem, could you use an IF that says "if the day is 30
or greater, then return the month number; if not, return the month
number minus one". To extend your example, suppose March 30 2005 is in
cell A1. The formula in B1 might be:
=IF(DAY(A1)>=30,MONTH(A1),MONTH(A1)-1)

However, I see some problems with this: February has only 28 days and
will never return its actual month number, and January 15, 2005 in cell
A1 will return the value 0. Is this consistent with your needs, or is
the month-end cutoff a certain number of days before the end of the
month? Should the Jan 15 entry return the previous month (12) or zero?

Either scenario can be accommodated- it's a matter of how you need the
results to display. Please let us know, and we can help.

 

[Guest]

Dave,

The formula works. The "30" was always an approximate date since the OLAP
update is never an exact date so I think changing that test to 28 works fine.
As far as delivering the 0 for January I can solve that with an IF/THEN
statement in the annualization formula so I don't get a "multiplied by zero"
result. In that case IF 0, use *12. Thanks for the help and the tutorial on
DAY MONTH. Fortunately it is far more intuitive than the MATCH/CHOOSE
learning curve.

Thanks again,
Kem