Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how do I change a date to show the year only? | Excel Worksheet Functions | |||
show date as day of the year | Excel Worksheet Functions | |||
Combination Graph with current year and prior year sales | Charts and Charting in Excel | |||
calculate prior calendar year end from a moving current date | Excel Worksheet Functions | |||
How to compare current year to prior year in bar chart? | Charts and Charting in Excel |