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.
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.
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.
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?
I found the CTRL J hint also works for “d/m h:mm” format
Thanks a lot…
How I use this for data I have been entered
Not sure what you mean, beyond my explanations in my post?
Dear Tom & Jon
Cntrl + J part is not clear to me – as to how to implement it.
Pl demonstrate.
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.
thanks Jon & Tom. I got this.
I tried & failed.
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.
I have wanted to use the Control+J in number formats for years now
Cool – – Thanks to Jon Peltier for that suggestion.
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