Show prior year based on date in other cell
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! |
Show prior year based on date in other cell
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. -- Gary''s Student - gsnu200857 "Munchkin" wrote: 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! |
Show prior year based on date in other cell
"Munchkin" wrote:
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 ----- "Munchkin" wrote in message ... 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! |
Show prior year based on date in other cell
=EDATE(B4,-12)
This formula is required ATP add-ins "Munchkin" wrote: 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! |
All times are GMT +1. The time now is 09:26 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com