Cell Shows a Date or Time Instead of a Number

Problem

In Excel, it is not uncommon for the user to call a function that should return a number, and find that the resulting value looks like a date or a time (eg. "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 may be because 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 altering the cell formatting.

The quickest way to do this 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):

Excel Format Cells Drop Down Menu on Ribbon


Change Formatting in Excel 2003

If you have an older version of Excel, that does not have the ribbon, you can change the formatting of an Excel cell via the following steps:

Excel Format Cells General Option
  • Select the cell(s) with the incorrect formatting;
  • Right click on the selected cell(s) with the mouse;
  • Select the Format Cells ... option from the right-click menu. This will cause the 'Format Cells' dialog box pop up.
  • Within the 'Format Cells' dialog box:

    • Ensure the Number tab is selected;
    • Under the Category: heading, select the option General (or Number) and click OK.


For further details on Excel formatting, see the Excel formatting page.