Excel 2013 - Cell with dd/mm/yyyy to be shown as mmmm in another along with text 1

[pendle666]

Hello everyone

I have cells L23 and L25 which are in the format dd/mm/yyyy.

Cell G25 and J25 have =L23 and =L25 in it respectively and formatted as mmmmm to show the months written in full.

But row 25 contains some instructions. If L23 is 15/12/14 and L25 is 15/01/15, what I want to say in J25 is

December and January

I know I can add in text to a cell using & "xxx", but because that cell is formatted to use the full month it doesn't work this time.

Can anyone help me ?

thank you



thank you for helping

____________
Pendle

 

[zelgar]

Use the following formula:

=IF(MONTH(L23)<>MONTH(L25),TEXT(L23,"mmmm")&" and "&TEXT(L25,"mmmm"),TEXT(L23,"mmmm"))

 

[pendle666]

Excellent, thank you very much!

thank you for helping

____________
Pendle

 

[pendle666]

Hello again

I've rejigged this a bit to show:

=IF(L23=0," ",TEXT(L24+29,"mmmm")&" and "&TEXT(L23,"mmmm"))

and it works just fine. However, if L23 doesn't have anything is in then I've got a #value! in there.

I've used ISNUMBER and ISBLANK before now to make the cell blank rather than having #value! in there so that the user doesn't think there is an error. But on this occasion ISNUMBER and ISBLANK isn't working for me. This is the formula: =IF(ISBLANK(L23)," ",TEXT(L24+29,"mmmm")&" and "&TEXT(L23,"mmmm"))

Looking at the help it would appear that it's because it's using text. Is there any way around this?

thank you

thank you for helping

____________
Pendle

 

[zelgar]

I'm guessing that the data that's contained in the cell is not a number but a text string. If the cell appears to be blank, there's probably a space in the cell. If there appears to be a date, it's probably has single (') or double quotes (") or there may be a space at the beginning.

Also, make certain that you check both cells (e.g., L23 & L24). Just trying a =TEXT(L23,"mmmm") for a blank cell gets me a result of January, but if it has any text the result is blank. If the formula is =TEXT(L24+29,"mmmm")) and it's blank you get January, but if it has text you get a #VALUE! error.

 

[pendle666]

Thank you Zelgar. Mega helpful as usual!

thank you for helping

____________
Pendle