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]

Maybe:

Sub test()
    Range("A:A").Replace "  ", vbNewLine
End Sub

I left two spaces as that is what is usually there when we have Date & Time

 

[Alex Blakenburg]

In VBA perhaps this:

format(date ,"dd/mm/yyyy") & vbnewline & format(time,"hh:mm")

In a formula putting in Alt+Enter seemed to work.

=TEXT(NOW(),"dd/mm/yyyy 
hh:mm")

 

[crug]

Thank you, the VBA solution does what i need except the format is
9/6/2022
2:00:00 PM

I've tried to format it - but it still returns the PM
[Range("E16").NumberFormat = "dd/mm/yyyy hh: mm]

 

[crug]

I dont think i can use the Formula solution as i take the dates from a different sheet via a dropdown - that has date add calculations included

 

[Alex Blakenburg]

OK you need to decide if you want the date as text or as a date.
As text you need to do all your formatting using the Format statement.
Once it is Excel as Text the Custom number formatting won't work since it only applies to numbers.

If you want to enter it into a cell as a real date (number) then you need something like this:

Sub EnterDateTime_2Lines()
    Dim rng As Range
    Set rng = Range("A10")
    rng = Date + Time
    rng.NumberFormat = "d/mm/yyyy " & Chr(10) & "h:mm"    
    rng.WrapText = True
    rng.EntireRow.RowHeight = 29
End Sub

 

[Georgiboy]

Another option (better as the date and time remain a number) is to format (or formula) the cells to have a few spaces between the date and time. Make sure you have textwrap set to on and you will get the below as long as the whole date/time string does not fit in the cell. See below:

Book1
	A	B
1	01/01/2022 13:00	01/01/2022 13:00
2	01/02/2022 13:00	02/01/2022 13:00
3	01/03/2022 13:00	03/01/2022 13:00
4	01/04/2022 13:00	04/01/2022 13:00
5	01/05/2022 13:00	05/01/2022 13:00
6	01/06/2022 13:00	06/01/2022 13:00
7	01/07/2022 13:00	07/01/2022 13:00
8	01/08/2022 13:00	08/01/2022 13:00
9	01/09/2022 13:00	09/01/2022 13:00
10	01/10/2022 13:00	10/01/2022 13:00
Sheet1

Cell Formulas
Range	Formula
B1:B10	B1	=TEXT(A1,"dd/mm/yyyy")&" "&TEXT(A1,"hh:mm")

 

[Alex Blakenburg]

(better as the date and time remain a number)

You know your column B is text and not a number, right ?

 

[Georgiboy]

They look that way on the result of XL2BB but see below, if it was text then it should not let me add a day to it.

Book1
	A	B	C
1	01/01/2022 13:00	01/01/2022 13:00	02/01/2022 13:00
2	02/01/2022 13:00	02/01/2022 13:00	03/01/2022 13:00
3	03/01/2022 13:00	03/01/2022 13:00	04/01/2022 13:00
4	04/01/2022 13:00	04/01/2022 13:00	05/01/2022 13:00
5	05/01/2022 13:00	05/01/2022 13:00	06/01/2022 13:00
6	06/01/2022 13:00	06/01/2022 13:00	07/01/2022 13:00
7	07/01/2022 13:00	07/01/2022 13:00	08/01/2022 13:00
8	08/01/2022 13:00	08/01/2022 13:00	09/01/2022 13:00
9	09/01/2022 13:00	09/01/2022 13:00	10/01/2022 13:00
10	10/01/2022 13:00	10/01/2022 13:00	11/01/2022 13:00
Sheet1

Cell Formulas
Range	Formula
B1:B10	B1	=TEXT(A1,"dd/mm/yyyy")&" "&TEXT(A1,"hh:mm")
C1:C10	C1	=B1+1

 

[Alex Blakenburg]

Interesting but not without risk.
Excel seems to be converting it on the fly if you apply an arithmetic operator to it but ISTEXT considers it Text and a Pivot Table considers it text, so does a Vlookup.