XL: How to Display the Current Date and Time with Text

This article was previously published under Q214093
This article has been archived. It is offered "as is" and will no longer be updated.
SUMMARY
You can enter the current date and time as text by using the TEXTfunction with the TODAY or NOW functions. For example, to display
Today is 2/18/98
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 preceding formula to display the date and time or just the time (depending on the number format that you specify).

You can also display
Today is 2/18/98
and preserve the value of the date as a serial number rather thanconverting it to text.

To display the current time, use the following formula:
=NOW()-TODAY()
This formula updates each time the sheet is calculated.
MORE INFORMATION
The TEXT() function converts a value to text in a specified numberformat.

To display the following text
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 text
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 informationbut you do not want to open the file, use the TEXT() function.

To display the time but preserve the value as a serial number, followthese steps:
  1. Select the cell that you want to format.
  2. On the Format menu, click Cells, and then click the Number tab. In the Category list, click Custom.
  3. In the Type entry box, type Today is (enclosed in quotation marks and with one space after "is") immediately followed by the date format that 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 displays
Today is m/d/yy
where m/d/yy is the current date and is displayed in the format that you selected in step 3. The value of the date is stored as a serial number.
concatenate XL2000
Properties

Article ID: 214093 - Last Review: 12/05/2015 12:28:38 - Revision: 2.2

Microsoft Excel 2000 Standard Edition, Microsoft Excel 2002 Standard Edition, Microsoft Excel 97 Standard Edition, Microsoft Excel 2001 for Mac, Microsoft Excel 98 for Macintosh

  • kbnosurvey kbarchive kbdta kbhowto KB214093
Feedback