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 (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 could have occurred because either:

or

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.

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):

Excel Format Cells Drop Down Menu on Ribbon With Date Option Selected


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 required formatting and click OK.


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