Excel date formatting

Question

Hello, I have a lot of date data to input into excel and the fastest way for me is to copy and paste. What I am copying is in the format 02/12 for example so excel corrects this too 02/12/22 which is perfect until I get to the 2023 dates. Does anyone know how I can get excel to auto correct to 2023 instead of the current year? Thank you! :)

Answer 1

Hi Rhiannon C!

Thank you for writing to the Microsoft Answer Community Forum. I am Shakiru, an independent advisor and a user like you, and I am glad to be helping you out today.

I'm sorry, you could do that, since we are currently in the year 2022, you will need to input the complete value.

or enter the data and then use then navigate to Find and replace the date "2002" to 2023

or you use the Date function of correcting the Year.=DATE(2023, 4, 3) + month

where month could be 1, 2, 3, and so on

KInd Regards, Shakiru

Answer 2

Hi Rhiannon. I am an Excel user like you.

The problem is that you must tell Excel which dates are not supposed to be 2022. There is no way for Excel to know that. Assuming that the dates are in order with all of the 2023 dates together, the easiest way to change them to 2023 would be to select those dates and then use Replace (CTRL+h) to replace 2022 with 2023.

Reply if you have additional questions or information. Please mark this reply as answered if this solves your question.

Rich~M

Answer 3

Wait 12 days?

Flippant, I know. What are you actually copying from? There may be different ways to do this.

As an aside, if you set the format of the cells you're pasting to to Text, Excel shouldn't try to be 'helpful' by changing the anything to dates, then you'll be able to manipulate the text after import.

There are numerous ways to import stuff into Excel; what version of Excel are you using? If you can share a file of what you're importing, all the better.