Cell Shows a Date or Time Instead of a Number
Problem
In Excel, you may sometimes call a function that you expect to return a number, and find that the function result looks like a date or a time (e.g. "01/01/1900", "02:00:00"), instead of an integer or decimal.
Possible Reason
This problem usually occurs because the cell containing your formula is formatted as a date or time instead of a 'General' type or a number.
This could have occurred because either:
- A date or time format has previously been applied to the cell
- You have inserted a new column next to a column containing Dates or Times, and the new column has 'inherited' the formatting of the adjacent column.
This formatting will cause Excel to display a simple number as a date or a time.
Solution
Note that it is only the cell formatting that is 'wrong' in this example, NOT the value returned by the function. Therefore, this problem is easily solved, by changing the cell formatting.
Method 1 - Formatting Through the Excel Ribbon Options
The quickest way to change the formatting in Excel is to select the cell(s) to be formatted and then select the required cell formatting from the drop-down menu in the 'Number' group on the Home tab of the Excel ribbon (see below):
Method 2 - Formatting Through the 'Format Cells' Dialog Box
If you require other types of cell formatting, that are not available in the drop-down menu on the ribbon, you may prefer to apply cell formatting using the 'Format Cells' dialog box. To do this:
- Select the cell(s) to be formatted;
-
Open up the 'Format Cells' dialog box by either:
- Clicking on the dialog box launcher in the 'Number' grouping, on the Home tab of the Excel ribbon (see rightabove).
- Using the keyboard shortcut Ctrl + 1 (i.e. press the Ctrl key and while holding this down press 1).
-
Within the 'Format Cells' dialog box:
- Ensure the Number tab is selected;
- Under the Category: heading, select the required formatting and click OK.
For further details on Excel formatting, see the Excel formatting page.