XL: DATE(), TIME(), and NOW() Return Formatted Numbers

ID: Q97098

The information in this article applies to:

Microsoft Excel for Windows, versions 4.0, 4.0a, 5.0
Microsoft Excel for the Macintosh, versions 4.0, 5.0

SUMMARY

The "Microsoft Excel Function Reference," version 4.0 and the "Online Help" version 5.0, state that the functions DATE(), TIME(), and NOW() return the serial number for their respective input values. This information is misleading. While it is true that these values are assigned a serial number, these values are also automatically formatted with date and time formats.

In versions of Microsoft Excel prior to version 4.0, the value for these functions is returned as a serial number without number or date formatting.

MORE INFORMATION

In Microsoft Excel version 4.0 and 5.0, the functions DATE(), TIME(), and NOW() return serial values that are automatically formatted in a date or time format. This formatting occurs when you enter the formula and will override any previous cell formatting.

In versions of Microsoft Excel earlier that version 4.0, the value returned is formatted with the current cell format (the number format applied to the cell or the General format if the cell is not otherwise formatted).

The following table indicates the number formats used by each function:


       Microsoft              Microsoft               Microsoft
       Excel 2.x              Excel 3.0               Excel 4.0, 5.0
       -------------------------------------------------------------

DATE   Current cell format    Current cell format     m/d/yy
TIME   Current cell format    Current cell format     h:mm AM/PM
NOW    Current cell format    Current cell format     m/d/yy h:mm

In Microsoft Excel version 4.0 or 5.0, to apply a different number format to a formula containing one of the above functions, you must apply the new format after the formula has been entered.

REFERENCES

"Online Help," version 5.0
"Function Reference," version 4.0, pages 93-94, 290-291, 433-434

Additional query words: 4.00a 5.0

Keywords :
Version : WINDOWS:4.0,4.0a,5.0; MACINTOSH:4.0,5.0
Platform : MACINTOSH WINDOWS
Issue type :