Date & time formatted on 2 lines in 1 cell

[crug]

Hello
Apologies if this has been solved previously - i have a number of dates on Sheet1, ie (=NOW() +1/48) and use those in Sheet2 as a dropdown list. The cell it goes into on Sheet2 is not wide enough for (06/09/2022 13:00:00) on 1 line. I need it to be Date & Chr(10) & Time as below.
06/09/2022
13:00:00

I cant seem to format using Format Cell correctly and wondered if i could with vba?

 

[Georgiboy]

It is interesting as you point out it does not react well in pivots etc... (Every day is a school day)

It is almost as if there is no point Excel converting it if it can only use it in specific scenarios. It also explains why XL2BB is stating that it is text.

I would discount that solution then as it is far from perfect.

The solution I would choose then is post 6 as it looks good and can be analysed.

 

[Alex Blakenburg]

In doing some research into this, you can achieve the column B lookup without VBA if you highlight column A, then go to
Format Cells > Custom > put in

dd/mm/yyyy Ctrl+J hh:mm

Then format with wrap text
and increase the column height.