Months going back from present

  • Thread starter Thread starter DavidS
  • Start date Start date
D

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
 
In A11 use

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

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)
 
DavidS said:
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.
Click to expand...

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.
 
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.
Click to expand...

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.
 
That approach gets problematical with 30th and 31st.

--
HTH

Bob Phillips

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