Months going back from present

[DavidS]

Hello, I am trying to create a column staring with the present month in A12,
going back 12 months to A1. I always want the present month to be in A12 and
then have the previous months going back to A1. I can use MONTH(TODAY()) to
get the month number and use a lookup for the actual month in A12. The
problem I have is that I can't think of a way to go back from January to
December. If I keep reducing the month number, I reach 0, which is not valid
i.e. A12 contains October and A3 January - I need to be able to put December
in A2 and have the column update and rollback when the month changes. Thanks

 

[Bob Phillips]

In A11 use

=A12-1+(A12=1)*12

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[joeu2004]

Hello, I am trying to create a column staring with the present month in A12,
going back 12 months to A1. I always want the present month to be in A12 and
then have the previous months going back to A1. I can use MONTH(TODAY()) to
get the month number and use a lookup for the actual month in A12. The
problem I have is that I can't think of a way to go back from January to
December.

Would this be simpler for you? Format A1:A12 as Custom "mmm" or "mmmm"
depending on whether you want Jan or January, for example. Then put
=today() into A12, put =date(year(A12),month(A12)-1,day(A12)) into A11,
and copy A11 through A1.

 

[joeu2004]

Errata....

Would this be simpler for you? Format A1:A12 as Custom "mmm" or "mmmm"
depending on whether you want Jan or January, for example. Then put
=today() into A12, put =date(year(A12),month(A12)-1,day(A12)) into A11,
and copy A11 through A1.

Put =date(year(a12),month(a12)-1,1) into A11. If you use day(A12), you
will get unexpected results when A12 (e.g) contains a day of the month
that the previous month (A11) does not have. For example, if A12 is
12/31/2006, my original formula would result in 12/1/2006 in A11.
Since your original approach did not attempt to retain the full date,
it should be okay to "normalize" all dates to the first of the month.

 

[Bob Phillips]

That approach gets problematical with 30th and 31st.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)