Date format not getting captured

[Mikeyounes]

Hello,

I really appreciate any help with this, as i've been doing it manually for quite a while now and its frustrating.

I have a list of dates with the format 01 Sep 2022 that i need to copy to excel. Whenever i try copying them, the date becomes 01/09/22 or 2001/09/22 if i highlight the cell. I just need the date copied to be correct, no matter the format. I tried changing the date format to custom in excel before copying with ddmmyyyy or dd/mm/yyyy but that doesn't seem to work. When i copy paste my date into word, i get 01/09/22, so the date gets into date format automatically from the software im copying it from. Its just excel that is distoring the date, even tho the format is correct.

Please help!!

Mike

[#2]

@Mikeyounes 

Do you want to store real dates in Excel or just texts? If the latter, format the destination column as Text before you Paste Special, Text.

Excel indeed tries to create dates for you, as it has great advantages when doing further work in Excel. Once the real date is stored in Excel you can display it in any way you like. So when you see 01/09/2022 in the formula bar, Excel has actually stored is as day number 44805 (day number 1 being 1 Jan 1900) and used your system settings to show that format in the formula bar. Use custom a format like 

dd mmm yyyy to display the date as 02 Sep 2022 .

Working with real dates enables you to calculate with them (e.g. duration) and use Excel's built-in time intelligence to create summaries by week, month, quarter, year, from-to etc.