Can't insert a carriage return into x-axis label text

Special K

Board Regular
Joined
Jun 20, 2011
Messages
62
I am using Excel 2010 and have a column of data consisting of dates/times like the following (column A):


Code:
12/28/2020  11:45:39 AM

I am trying to create an XY scatter plot with X-axis labels in which the date is displayed on the first line and the time is displayed on the second line. I have done some searching and found threads such as these that explain how to do it:


however when I try the methods explained in that thread, they don't work. For example, I made a new column, B, containing the following for each row in column A:

Code:
=TEXT(A2,"mm-dd-yyyy
hh:mm:ss AM/PM")

Yet when I create a plot using column B as the x-axis values, double-click the x-axis labels, and then choose Number->Custom->"mm-dd-yyyy h:mm:ss AM/PM" for the format of the x-axis labels, the resulting labels are still only on one line and don't even display the correct dates/times (the first visible x-axis label is "01-00-1900 12:00:00 AM".

I then tried to use the CTRL+J trick described in that thread but it didn't work either. To do this I double-clicked the x-axis labels, chose Number->Custom->"mm-dd-yyyy h:mm:ss AM/PM", placed the cursor after the "yyyy" and typed "CTRL+J" (without the quotes), but nothing happened (i.e. it wouldn't let me add a new format after typing "CTRL+J").

I also found this SO thread in which one of the posters said that manually adding "ALT+ENTER" into the cell itself at the appropriate spot (i.e after the yyyy in my case) would automatically propagate to the x-axis labels, but I tried that and it didn't work.
 

Some videos you may like

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)

Tetra201

MrExcel MVP
Joined
Oct 14, 2016
Messages
3,631
Try custom-formatting the first X value in column A (or all X values) as mm-dd-yyyyCtrl+Jh:mm:ss AM/PM
 

Special K

Board Regular
Joined
Jun 20, 2011
Messages
62
Try custom-formatting the first X value in column A (or all X values) as mm-dd-yyyyCtrl+Jh:mm:ss AM/PM

Thanks, that worked. For some reason the CTRL+J trick doesn't work when you try to reformat the axis labels themselves, but it does work on the actual cells.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,472
Messages
5,596,354
Members
414,060
Latest member
hermanseck

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top