If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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! |
#2
|
|||
|
|||
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! |
#3
|
|||
|
|||
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! |
#4
|
|||
|
|||
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! |
Thread Tools | |
Display Modes | |
|
|