Excel: Displaying the Current Date and Time with Text

 Last reviewed: February 2, 1998
Article ID: Q95951

The information in this article applies to:
  • Microsoft Excel for Windows, versions 2.x, 3.0, 4.0, 4.0a, 5.x
  • Microsoft Excel for Windows 95, versions 7.0, 7.0a
  • Microsoft Excel 97 for Windows
  • Microsoft Excel for the Macintosh, versions 2.x, 3.0, 4.0, 5.x
  • Microsoft Excel 98 Macintosh Edition
  • Microsoft Excel for OS/2, versions 2.2, 3.0

SUMMARY

You can enter the current date and/or time as text by using the TEXT function with the TODAY or NOW functions. For example, to display "Today is 2/18/98" (without the quotations marks), where 2/18/98 is the current date, use the following formula: 

   ="Today is "&TEXT(TODAY(),"m/d/yy")
NOTE: You can substitute the NOW function for the TODAY function in the above formula to display the date and time or just the time (depending on the number format you specify).

You can also display "Today is 2/18/98" (without the quotation marks), and preserve the value of the date as a serial number rather than converting it to text.

To display the current time, use the following formula: 

    =NOW()-TODAY()
This formula will update each time the sheet is calculated.

MORE INFORMATION

The TEXT() function converts a value to text in a specified number format.

To display the following 

   The current date and time is 2/18/93 5:57 PM
use this formula: 

   ="The current date and time is "&TEXT(NOW(),"m/d/yy h:mm AM/PM")
To display the following 

   The current time is 5:57 PM
use this formula: 

   ="The current time is "&TEXT(NOW(),"h:mm AM/PM")
If you want a linked formula in a chart to display this information but you do not want to open the file, use the TEXT() function (as discussed above).

To display the time but preserve the value as a serial number, follow these steps:

  1. Select the cell you want to format.

  2. On the Format menu, click Number. (Or, click Cells on the Format menu, and then click the Number tab.) In the Category list, click Date or Custom.

  3. In the Code or Type entry box, type "Today is " (including the quotation marks and one space after "is") immediately followed by the date format you want to use (for example, m/d/yy, or d/mmm/yy), and then click OK.

  4. In the formatted cell, type =TODAY().

The cell will display "Today is m/d/yy" (where m/d/yy is the current date and is displayed in the format you selected in step 3). The value of the date is stored as a serial number.

REFERENCES

"Microsoft Excel Function Reference," version 4.0, page 431-432 "Microsoft Excel Function Reference," version 3.0, page 234-235

Additional query words: XL98 XL97 XL7 XL5 XL4 XL3 5.00 3.00 4.00
concatenate
Keywords : xlformula
Version : WINDOWS:2.0,3.0,4.0,4.0a,5.0,7.0,97; MACINTOSH:2.0,3.0,4.0,5.0,98; os/2:2.2,3.0
Platform : MACINTOSH OS/2 WINDOWS
Issue type : kbhowto kbinfo

THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.

Last reviewed: February 2, 1998
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.