Return a date one year prior. MS XL


I am using XL 2010 . a column had dates entered in it. However, they entered the wrong year.  they entered 2014. It should have been 2013.   the format is  11/07/2014.  I need that to read    11/07/13.

can u help?
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
let's say A1 = 11/07/2013, then try:


and format as date, or simply try:


David Johnson, CD, MVPOwnerCommented:
=DATE(YEAR(A1)-1,MONTH(A1),DAY(A1))  is preferred as you may have a leap year so days-365 would be incorrect.
Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
yup, disregard my 2nd suggestion as it could be incorrect. but you can tell us if you would like to handle such cases if necessary >> if there were wrong dates keyed in as 29 Feb 2008, 29 Feb 2012, etc. how you want it to be correct. if this is impossible to be happened in your data, you can simply ignore this.
[ fanpages ]IT Services ConsultantCommented:
Select the column (or the range) with the incorrectly entered dates.

Use the [CTRL]+[H] key combination to show the "Find and Replace" dialog box.

Enter the following...

Find what:

Replace with:

(Please note that even with data entry of a two-digit year, the search criteria is most likely to be a four-digit year.  Obviously, if this fails, please use /14 and /13 respectively.  Sorry, but your question text swapped between four digits & two digits, & I am not sure if that was intentional, or not, so amend as necessary for your requirements.)

Click the [Options > >] button to reveal the additional fields (below), & ensure they are set to match...


By Columns

Look in:

Match case
[ ] (not checked)

Match entire cell contents
[ ] not checked

Click the [Replace All] button if you are feeling confident, otherwise use the [Replace] button & confirm each change as the search progresses through the range of data.

Find and Replace dialog
>> ... the format is  11/07/2014.  I need that to read    11/07/13 ...

Just an aside (nothing to do with helping to provide a solution); this doesn't actually tell you which format you want:

In the UK, these dates would be interpreted as 11th day of 7th month (July).
In the US, these dates would be interpreted as 7th day of 11th month (November).
Most (although not all) of the rest of the world tends to use the ISO date format yyyy-mm-dd (or yyyymmdd without the separators); this has the advantages of being less ambiguous, and also providing a natural sort order.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.