Wrapping Date/Time in Cell

[Jeffrey Mahoney]

I want to display the date on the first line and the time on the second line in a cell.

Since Excel won't wrap a number in a cell and Date/Time values are numbers, all you get is ### in a column when the number is too long for the column width.

Is there any way to add a hard return in the number formatting string?

 

[sandy666]

Wrap text
Alt Enter between date and time
change width of column

01/04/2020 23:25:00

 

[steve the fish]

Change cell to wrap text. Custom number format enter dd/mm/yyyy then press CTRL-J then hh:mm works for me

 

[Jeffrey Mahoney]

@sandy: Thank you, I know about Alt-Enter. I have a ton of new data coming into this database I don't want to have to create a routine to edit the cells

@steve the fish: Nice! Didn't know about Ctrl-J. It kinda hides the time after pressing Ctrl-J, but not a problem to deal with it.

Thank you both.

 

[Jeffrey Mahoney]

Oh, bad news. Excel doesn't know that the time wrapped below and still puts ### when the column is too narrow. There's plenty of space, but it refuses.

 

[Jeffrey Mahoney]

Oh, and just for your reference Sandy, If you add the hard return, the date/time is turned into a string. No more calculations can be performed

 

[Peter_SSs]

Work-around suggestion
- Add another column as shown, with wrap text.
- Hide column B
- Do your calculations still referencing the hidden column B

20 04 02.xlsm
	B	C
1	Date Time	Date Time
2	2/04/2020 11:39	2/04/2020 11:39
Date Time

Cell Formulas
Range	Formula
C2	C2	=TEXT(B2,"d/mm/yyy"&CHAR(10)&"hh:mm")

 

[Jeffrey Mahoney]

Thanks Peter. I have some code to import daily reports. I could incorporate a new column into that. The problem is that autofilter doesn't see that as a date; sorting becomes wonky, because if I don't have the format in yyyy/mm/dd it sorts January 2020 before December 2019. Each date becomes a stand-alone string not related to month or year in the autofilter.

Sorry everyone. It seems I'm just here punching holes into your ideas. I could divide the columns into Date and Time, but I don't think I'll reduce any precious column width.

 

[Peter_SSs]

The problem is that autofilter doesn't see that as a date; sorting becomes wonky

Keep the real date/time column visible as well, but very narrow if you want then you can still use its AutoFilter. You could even format text in this column same as background if you don't want the look of the ###

or

Add yet another column that ranks the original Date/Time values and use that to sort. The original column could then be hidden or even deleted since you are using code and could make both of the new columns actual values instead of formulas.

20 04 02.xlsm
	B	C	D
1	D/T	Date Time	Rank
2	###	9/06/2020 12:00	7
3	###	13/04/2020 04:48	2
4	###	11/06/2020 02:24	8
5	###	22/04/2020 19:12	4
6	###	19/03/2020 12:00	1
7	###	23/04/2020 14:24	5
8	###	5/06/2020 12:00	6
9	###	17/06/2020 16:48	9
10	###	17/04/2020 07:12	3
Date Time

Cell Formulas
Range	Formula
C2:C10	C2	=TEXT(B2,"d/mm/yyy"&CHAR(10)&"hh:mm")
D2:D10	D2	=RANK(B2,B$2:B$10,1)

 

[Excel Pro Tips Guy]

Oh, bad news. Excel doesn't know that the time wrapped below and still puts ### when the column is too narrow. There's plenty of space, but it refuses.

Not sure if you are still looking for this, but an extra step needs to be taken to make the date time wrap correctly.

In my example I used
mm/dd/yy Ctrl-J
hh:mm AM/PM

After you add the format follow these steps.

1. On the alignment tab uncheck "Wrap Text" if it is already selected
2. Select "Shrink to fit"
3. Reselect "Wrap Text"

"Shrink to fit" will be greyed out when you select "Wrap text", but it is consistently resolving the wrap issue I am having.