Hi Aleksandrs

When you reach the third step of text-to-column, select the column with the date.

Under Column data format, click on Date and select the format you want and click Finish.

Format the output to the date format you require.

You can read more here :  https://www.myonlinetraininghub.com/excel-date-and-time

Refer to attachment.

Hope this helps.

Sunny

Hi, SunnyKow!

Thank you for your reply! I've followed the steps,  screenshot:

https://www.screencast.com/t/W3nhlSiDhkVb

But it is still not working for me.

I've attached the resulting sheet.

I'm probably missing a step. I've noticed in the file you've attached a dot is used as a separator and month is not 3 letters, but numeric (dd.mm.yyyy), but I still have a hyphen and month displayed as 3 letter abbreviation after I follow the steps you've outlined. Can this be the reason?

[Image Can Not Be Found]

I sometimes encountered the similar problem. In your case Excel can calculate with the dates in column A! (put another date in column B and substract that one from the one in A and it calculates the days in between as expected).

So it recognises the values as dates. But you can't display them in another format such as dd-mm-jjjj or so. It seems something is holding it up.

Also copy-paste it somewhere else, or try to get the value in another cell doesn't work.

I'm curious what causes this behaviour and what to do about it.

Hi Aleksandrs,

Try using Text to Columns but in step 3 of the wizard change the destination to $B$1. I'm wondering if column A is formatted as text because that's how the data came in. Might just need to put it in an empty column that doesn't have any formatting attached.

Like Frans says, Excel can tell they're dates in some calculations, so if my suggestion above doesn't work then you can try copying any empty cell > select the dates > Paste Special: Values and Add.

Let us know how you get on.

Mynda

Hi Aleksandrs

I used the data from the CSV file and not from your worksheet to do the text-to-column.

Excel already recognize them as dates as mentioned by Frans.

If you want to extract only the date portion, then in B2 you can enter INT(A2).

You will then need to apply the correct formatting e.g. dd-mmm-yyyy or dd.mmm.yyyy etc. (you will need 3 m's) to display the 3-letter month. 

Mynda's blog on Date and Time provide many good examples.

https://www.myonlinetraininghub.com/excel-date-and-time

Hope this helps.

Sunny

Frans Visser said
Also copy-paste it somewhere else, or try to get the value in another cell doesn't work.

Tried. Doesn't work.

Mynda Treacy said 

Try using Text to Columns but in step 3 of the wizard change the destination to $B$1. 

Mynda Treacy said 

you can try copying any empty cell > select the dates > Paste Special: Values and Add

That didn't work either.

Even tried to copy the data to notepad and then copied it into a new spreadsheet.

Also if Excel recognizes data as date why it doesn't sort it ascending/descending?

Can you sort dates in file previously attached (raw2.xlsx) ascending/descending? I can't. For me, it doesn't recognize it as dates and that is the problem.

I believe I know what the problem is:

Excel doesn't recognize this particular format - 8-Sep-2017 - due to some settings issue or the conflict between Excel local setting (I have an English language copy) and Windows local settings (Windows runs in Russian with localized decimal and dates settings).

If I do the following:

8-Sep-2017

Find and replace dashes with dots

8.Sep.2017

Find and replace "Sep" with "09"

8.09.2017

Excel starts to recognize it as dates.

Any idea what settings I need to change in Excel to fix this conflict? I can't run a Russian copy of Excel cause when I won't be able to follow Mynda's course as all formulas are translated as well and look totally different (and obviously, menus too) 🙂

Thank you for your help!

Hi Aleksandrs,

Have you tried using Power Query to get the data and then set the data type for the column with 'Change using locale' as shown in lesson 4.03 of the Power Query course?

Otherwise, you could use Power Query to find and replace the dashes with dots and then changing the data type. At least with Power Query you'll be automating the process for future, whereas using Find & Replace in Excel has to be done manually each time, unless you write some VBA to automate it, but it's easier to automate with Power Query.

If you get stuck please upload a sample Excel file with your dates and we can insert the query for you.

Mynda