Tom’s Tutorials For Excel: Putting a Date on One Line and Time on Another Line in the Same Cell

Tom’s Tutorials For Excel: Putting a Date on One Line and Time on Another Line in the Same Cell

Here’s a tip to place the date and time in the same cell, in separate lines.

The first step is to enter the formula
=TEXT(NOW(),"MMMM D, YYYY")&CHAR(10)&TEXT(NOW(),"HH:MM AM/PM")
The CHAR(10) notation refers to ascii character number 10, which is a carriage return character. In the cell, it looks like a small square, for example as pointed to by the red arrow.

Next,right-click the cell and select Format Cells from that pop-up menu.

In the Format Cells dialog box, click onto the Alignment tab. Click the dropdown arrow for the Horizontal field in the Text alignment section, and select Center.

Lastly, select the option for Wrap text, and click OK.

Share Button
Posted in Tom's Tutorials for Excel
Tags: , , , , , , , , , , , , ,
15 comments on “Tom’s Tutorials For Excel: Putting a Date on One Line and Time on Another Line in the Same Cell
  1. Jon Peltier says:

    Here’s a better way. Use a custom number format of
    mmmm d, yyyy h:mmAM/PM
    in which indicates the place where you type ctrl+j in the middle of the format string to produce a line feed in the formatted number.
    This way is better because it preserves the numeric value of the date in the cell, so you can use it for subsequent calculations.
    Microsoft never documented this ctrl+j trick, and in fact it’s broken in number formats for charts in later versions of Excel.

    • Tom Urtis says:

      Thanks Jon. So the difference is Ctrl+J instead of CHAR 10 in the format string, and no need to force the time as a text from its actual numeric value if it comes that way such as a static (or non-static) NOW function. True, very little info on Ctrl+J. I’ve been using Ctrl+J in the TextToColumns dialog as the Other delimiter for parsing strings at line feed Alt+Enter.

    • M. Miranda says:

      Jon Peltier, thanks for you {GREAT} hint!
      I would like to use a similar tip for format “d/m h:mm” (without AM/PM marks).
      Do you know any idea?

    • Saumya says:

      Thanks a lot…

  2. Prasanth says:

    How I use this for data I have been entered

  3. sandeep kothari says:

    Dear Tom & Jon
    Cntrl + J part is not clear to me – as to how to implement it.
    Pl demonstrate.

  4. sandeep kothari says:

    I tried & failed.

    • Tom Urtis says:

      You implement Ctrl+J by pressing the Ctrl key and while doing that, press the J key and then release both the Ctrl and J keys. How did you fail at doing that.

  5. John Reece says:

    I have wanted to use the Control+J in number formats for years now

  6. Matt says:

    I did it in Format Cells/Custom…ddd:mm/dd/yy. Put cursor after colon, hit ctrl+J and OK to select.
    You should get this… Mon :
    07/06/20

Leave a Reply

Your email address will not be published. Required fields are marked *

*