Microsoft Excel offers different options to format the content in cells. Using proper cell format is very important while working with Excel spreadsheets. Otherwise, Excel will consider a number you enter as a plain text. If you see all the dates as numbers in Excel, here is how you can convert them to proper date format.
Why to Use Proper Date Format?
Excel has hundreds of features for data processing. Some features are in the form of tool bar or menu items while there are many other functions you can use to build a formula. However, these features work based on the cell format you have used in the spreadsheet. Excel offers text, number, date, currency, percentage, fraction and many other formats. For example, you can use the followings with proper date format which are NOT possible if the cells are in text or number format.
- Sorting of columns based on dates.
- Subtracting or adding dates.
- Filter data based year or month or date.
- Data analysis based on dates.
Storing Dates in Excel
The default date display in Excel depends on the regional settings from your computer. However, when you enter the date in different format (like 24 Jan 2024), the cell will automatically use “Custom” format. It is also possible; the date cell’s formats are wrongly changed to “Text” or “Number” causing the following problems.
- Dates look like numbers.
- Cells with dates show a green triangle on top left corner of the cell.
- Shows in different date format like 23/05/24 which may confuse you as do not know which is the year or date.
- Formula errors due to wrong format.
Good part is that regardless of what you see, Excel stores the date as a sequential serial number from 01 Jan 1900 (or 01 Jan 1904) to 31 Dec 9999. For example, 01 Jan 1900 is stored in Excel as 1 and 01 Jan 2024 is stored as 45292. You can use one of the following methods to convert the serial number to proper date format as you like.
1. Dates Show as Numbers in Excel
When the cells use other formats like “General”, “Number” or “Text”, Excel will show the dates as numbers like below. You can change the cell formatting in column B to convert the numbers into dates. There are multiple ways to change the cell formatting in Excel. We explain here with Microsoft 365 Excel for Mac version and the steps are same in Excel for Windows version also.
1.1. Using Copy Paste Formatting
You change the formatting of first cell (B2) and copy paste on other cells (B3 to B6). Though our example is simple, this copy pasting of formatting is useful when the cells are scattered over different columns in the sheet.
- Right-click on the first date cell that you want to show it properly.
- Select “Format Cells” option from the context menu to open “Format Cells” pop-up. Alternatively, you can press Command + 1 in Mac or Control + 1 in Windows to open “Format Cells” pop-up.
- Go to “Number” tab and change the “Category” to “Date”. Select the date format you want to display under “Type” section and click “OK” to close the pop-up.
- Now, the selected cell will show the date instead of number and copy that cell (B2) by pressing “Control + V” or “Command + V”.
- Select all other cells showing numbers (B3 to B6) and paste by pressing “Control + V” or “Command+ V”.
- Click on the small icon beside the last cell showing as “Paste Options”.
- This will show lot of options and select “Formatting” to apply date format to other cells also. On Excel Windows version, you will see icons instead of text menu and select “Formatting” icon under “Other Paste Options”.
- This will apply the date formatting to all selected cells and convert the number into date proper format.
Remember, you can change the date display to any formats like 22-Jan-24 or 22/01/24 or 01/22/24 from the “Format Cells” pop-up. The selected format will be applied to all the cells when you paste.
1.2. Select Cells and Apply Date Formatting
In this method, select all the cells (B1 to B6) and apply the formatting at once.
- After selecting the cells, go to “Home” menu and click on the cell format dropdown. Select “Long Date” or Short Date” format to convert the number into date.
- If you want to show different display format other than long or short date form, right-click and open “Format Cells” pop-up. There you can select the category as “Date” and set the “Type” as explained above.
2. Date Cells Show with Green Triangle
Sometimes, you will see a green triangle in the cells. This indicates an error in the date due to wrong cell formatting or using 2 digits year instead of 4 digits. Simply click on the cell to see the warning message showing as “This cell contains a date string represented with only 2 digits for the year”.
In this case, click on the warning icon and select the action to fix the error. The warning symbol and triangle in the cell will disappear once the error is fixed. If the symbol is not going away, simply use one of the methods explained in option 1 to change the cell to correct date format.
3. #VALUE! Formula Errors
When you reference cells with wrong date formats in date related formulas, Excel will show #VALUE! error as it can’t calculate the result.
Click the warning icon and select “Trace Error” option to find the referenced cells in that formula. This is useful if you have not created the sheet to easily find the references with arrow pointing from the cells.
Click on the referenced cell and change it to date format as explained above. This will fix the formula error and show the correct result.
4. DATEVALUE Issue
DATEVALUE function is useful to get the serial number for dates from a cell value. However, the cell SHOULD be in a plan text format for this to work. In this case, you SHOULD NOT use date format even though the cell contains a date. For example, referencing 20 Jan 2024 in DATEVALUE function will show the serial number as 45311 only when the referenced cell is in “Text” format. otherwise, you will see #VALUE! error.
