Excel date format question?

Question

I have an excel table with dates in dd.mm.yyyy format, and I can't get excel to detect these as dates. I already created a custom date format, but all I achieved with this is that whenever I put a date in dd/mm/yyy format, it automatically transforms it into dd.mm.yyyy, and thats not what I want. I want excel to identify the date column as such.

Answer 1

Excel version???



That is one of excel's annoyances. Try this, no harm in doing it:



• Select the column/range of cells with the dates.

• EXCEL 2003: From the menu bar select DATA >> TEXT TO COLUMNS

• EXCEL 2007/2010: Choose the DATA tab. On the DATA TOOLS group click TEXT TO COLUMN

• Click FINISH.

• Select the the column/range of cells with the dates

• Define your custom format



That should work

Answer 2

in case you positioned 12/22/09 and format it as a date you've numerous procedures for excel to show the date. truly all excel sees is your get admission to the format is only a view, you may format it to assert December, 22, 2009. in case you want to carry close what excel sees and makes use of look on the bar on the right at the same time as your on that cellular no count number how this is formatted. once you variety this is going to variety utilising the numbers you typed and excel sorts via month, then day then three hundred and sixty 5 days both ahead or backward. in case you enter the three hundred and sixty 5 days first it receives perplexed. in case you positioned a ' it thinks it textual content and could no longer format it as a date or a huge decision.

Answer 3

Your dates, and maybe the rest of your table, is formatted as text. Highlight all the dates and on the Data tab/ Data Tools panel, select text to columns. After that, choose the format or create the custom format for the dates that you want.



If there are numbers in the table and you want to do calculations with them, be sure that they are not in TEXT format and follow the same procedure to change them to real numbers.