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:

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.

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

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


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:

    Dialog Box Launcher in the Number Group of the Excel Ribbon
    • Clicking on the dialog box launcher in the 'Number' grouping, on the Home tab of the Excel ribbon (see rightabove).
    or
    • Using the keyboard shortcut Ctrl + 1   (i.e. press the Ctrl key and while holding this down press 1).
  • Excel Format Cells Dialog Box With General Category Selected

    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.