The items are left-aligned - dates (numbers) are right-aligned by default.Here are a few more signs that the items in column C are being treated as text: Long Date format does the same thing - it just shows the cell contents.Īlthough the entries in column C look like dates, Excel sees them as text, not real dates.Īnd that's why the imported dates won't change format - Excel will not apply number formatting to text.Short Date preview just shows the cell contents.However, if you select a cell with one of the imported dates that won't change format, the Number format commands show that there is a problem: Learn more about the Excel date systems on the Microsoft website. The latest date allowed for calculation in Excel is, which is the number 2958465.The number zero is formatted as January 0, 1900, which is calculated as December 31, 1899.

In Excel, you can format positive numbers in any of the date formats, to see the date that they represent.Here are a few notes on formatting numbers as dates: Below that, the Long Date format is showing the weekday name, and full month name for the date -Sunday, January 1, 1900.If you type the number 1 in an Excel worksheet cell, then point to the Short Date format on the Excel Ribbon, you can see that date in the formatting preview.Since Mac Excel 2011 it is in line with the Windows date.Up to and including Excel 2008, the date system started on January 1, 1904.The date system in Microsoft Excel for Windows starts on January 1, 1900.

There are date system differences in some versions of Excel. If you format negative numbers as dates, the cells will be filled with number signs. Note: Only positive numbers can be formatted as valid dates. The imported dates are probably stored as a text value, instead of real numbers, and that is causing the formatting problem. In Excel, dates are stored as sequential serial numbers.

Why won’t the imported dates change format? Why won't they show a short date, without time included, in the cell's formatting? Those dates stay in the same date/time format, no matter how you try to format cells that contain those dates. However, the date format for the imported dates in column C does not change.Using the Number format commands on the Excel Ribbon Home tab, the Short Date format has been applied, from the number format menu.The time shows hour, minute and second = hh:mm:ss with AM or PM. The date is in short date format - d/m/yyyy. In the screen shot below, Column C contains imported dates, which show the date and time, separated by a space character.

How to get excel on mac