Show prior year based on date in other cell

M

Munchkin

Is there any way to copy a full date (mm/dd/yyyy), past it into the cells
below, but have the year change to the prior year in descending order?

Specific info: B4 shows the current mm/dd/yyyy of a file. Depending on how
many years we have had this account we want B5 through B?? to show same
mm/dd, but prior yy in descending order.

Right now we are copying B4 & pasting in the rows below, then manually
changing the year in each cell - is there an easier to do this?

Thanks much!
 
G

Gary''s Student

If A1 contains a date, then in A2 enter:

=DATE(YEAR(A1)-1,MONTH(A1),DAY(A1)) and copy down

just be careful of Feb and leap years.
 
J

JoeU2004

Munchkin said:
Is there any way to copy a full date (mm/dd/yyyy), past it into the cells
below, but have the year change to the prior year in descending order?

One way.... Put into B5 and copy down:

=date(year(B4)-1,month(B4),day(B4))

Caveat: That does not work well when B4 is Feb 29 and the number of
previous years exceeds 3. For that, use:

=min(date(year(B4)-1,month(B4),day($B$4)), date(year(B4)-1,1+month(B4),0))

and format as Date or a Custom date format.


----- original message -----
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top