| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Amend year
I have data for 2008 which originally was just day and month (eg, 12 Jan)
which when input into Excel 2007 has become day, month and year, eg, 12 Jan 09. As this should be 2008 instead, is there a formula where I can minus one year from the date? Thanks. |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Amend year
=DATE(YEAR(A1)-1,MONTH(A1),DAY(A1))
On 15 Sty, 16:22, E wrote: I have data for 2008 which originally was just day and month (eg, 12 Jan) which when input into Excel 2007 has become day, month and year, eg, 12 Jan 09. As this should be 2008 instead, is there a formula where I can minus one year from the date? Thanks. |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Amend year
Put a full date in the cell (you can use any day since you don't care about
the day... I'd use the 1st) and then use this custom cell format... dd mmm to make it display the way you want. -- Rick (MVP - Excel) "E" wrote in message ... I have data for 2008 which originally was just day and month (eg, 12 Jan) which when input into Excel 2007 has become day, month and year, eg, 12 Jan 09. As this should be 2008 instead, is there a formula where I can minus one year from the date? Thanks. |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Amend year
Thanks, just what I needed.
"Jarek Kujawa" wrote: =DATE(YEAR(A1)-1,MONTH(A1),DAY(A1)) On 15 Sty, 16:22, E wrote: I have data for 2008 which originally was just day and month (eg, 12 Jan) which when input into Excel 2007 has become day, month and year, eg, 12 Jan 09. As this should be 2008 instead, is there a formula where I can minus one year from the date? Thanks. |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Amend year
Thanks, but I'm pivoting so I need the correct year.
"Rick Rothstein" wrote: Put a full date in the cell (you can use any day since you don't care about the day... I'd use the 1st) and then use this custom cell format... dd mmm to make it display the way you want. -- Rick (MVP - Excel) "E" wrote in message ... I have data for 2008 which originally was just day and month (eg, 12 Jan) which when input into Excel 2007 has become day, month and year, eg, 12 Jan 09. As this should be 2008 instead, is there a formula where I can minus one year from the date? Thanks. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| amend formula | Excel Discussion (Misc queries) | |||
| how can I amend my address data source | New Users to Excel | |||
| how to amend pivot table structure | Excel Worksheet Functions | |||
| Printing: how do I amend my print defaults? | Excel Discussion (Misc queries) | |||
| Amend formula to include addition condition | Excel Worksheet Functions | |||
![E[_2_] is offline](https://www.excelbanter.com/images/SultanThemeVB3R/statusicon/user_offline.gif)
Linear Mode
