Wrap text for date values

[PATSYS]

Hi,

I have a date value 9/1/2019 in a cell that is formatted as MMM YYYY so it appears as Sep 2019. I would like the year to appear on a 2nd line in the same row.

So instead of

Sep 2019

It should be:

Sep
2019

How can this be done?

Seems wrap text doesn't work on dates.

Thanks

 

[Rick Rothstein]

I have a date value 9/1/2019 in a cell that is formatted as MMM YYYY so it appears as Sep 2019. I would like the year to appear on a 2nd line in the same row.

Select the cell and call up the Cell Formatting dialog box... change the Custom cell format Type pattern to MMMYYYY (no space) then click between the last M and first Y and type CTRL+J (it will look like the YYYY was deleted... it wasn't... it move down below the field where you can't see it)... click the OK button. Now turn on Wrap Text if you haven't already done so and you will probably have to make the cell taller in order to see both lines of the date.

 

[Giordano Bruno]

Neat trick Rick. I've tried to do that before and thought it was not possible.

 

[mohadin]

Hi PATSYS ,
What about for one cell

Sub test()
dt = Split(Format(Range("a1"), ("MM YYYY")), " ")
[b1] = MonthName(dt(0), True) & Chr(10) & dt(1)
End Sub

 

[mohadin]

And for range of dates in column (A)

Sub test()
lr = Cells(1, 1).CurrentRegion.Rows.Count
Set Rng = Range(Cells(1, 1), Cells(lr, 1))
i = 0
For Each cell In Rng
dt = Split(Format(Range("a1"), ("MM YYYY")), " ")
Rng.Offset(, 1) = MonthName(dt(0), True) & Chr(10) & dt(1)
i = i + 1
Next
End Sub

 

[Rick Rothstein]

Hi PATSYS ,
What about for one cell

Sub test()
dt = Split(Format(Range("a1"), ("MM YYYY")), " ")
[b1] = MonthName(dt(0), True) & Chr(10) & dt(1)
End Sub

Just pointing out that if the OP wanted the two-line date displayed in the adjacent cell, then your above code could be replaced with just a single of code...

Sub Test2()
  [B1]=format([A1],"mmm" & vblf & "YYYY")
End Sub

Of course, you do not need any code to do this as you can just put this formula in cell B1 directly..

=TEXT(A1,"mmm"&CHAR(10)&"yyyy")

although you would have to manually turn Wrap Text on for it to display on two lines.

And for range of dates in column (A)

Sub test()
lr = Cells(1, 1).CurrentRegion.Rows.Count
Set Rng = Range(Cells(1, 1), Cells(lr, 1))
i = 0
For Each cell In Rng
dt = Split(Format(Range("a1"), ("MM YYYY")), " ")
Rng.Offset(, 1) = MonthName(dt(0), True) & Chr(10) & dt(1)
i = i + 1
Next
End Sub

And this code could be replaced by this...

Sub test()
  With [A:A].SpecialCells(xlConstants)
    .Offset(, 1) = Evaluate("IF({1},TEXT(" & .Address & ",""mmm""&CHAR(10)&""yyyy""))")
  End With
End Sub

 

[mohadin]

Hi Mr. Rothstein
Thank you very much for you time, I'm glad for review my post

- You are right about the code for one cell
- The formula is not working on excel 2013, actually gives the same thing 19/1/2019>>>>19/1/2019
- As well as for the second code you provided (for range)
I'm sorry to say that, but is it some thing wrong I'm doing Or...
looking forward hear from you

 

[mohadin]

Hi Again
well, forget about the date format in #1
Any way What I found that is your code (for range) is correct in this case but If the format is not (Jan 2019) will not be
while my code works what ever the format is.
Still the formula Issue not working here what so ever

 

[Rick Rothstein]

@mohadin,

I am not entirely sure why you are having a problem with my code. It works perfectly for me (using XL2010). The code is placing Text values into the cell adjacent to the date cell... as a Text value, I do not see why the cell's format should be affecting it.

 

[PATSYS]

Select the cell and call up the Cell Formatting dialog box... change the Custom cell format Type pattern to MMMYYYY (no space) then click between the last M and first Y and type CTRL+J (it will look like the YYYY was deleted... it wasn't... it move down below the field where you can't see it)... click the OK button. Now turn on Wrap Text if you haven't already done so and you will probably have to make the cell taller in order to see both lines of the date.

Cool trick, thanks Rick!