function bar for C3 cell to equal C2 cell

[data808]

I have a cell C3 that is a date formatted cell like this: mm/dd/yy

I would like cell C2 to only pull the month from that C3 cell and spell out the whole word. I been typing in the function bar like so:
=format(range("C3), "mmmm" and its not working cause it thinks its a formula from the equal sign but I don't know what else to use.

Can anyone help me with this? Thanks.

 

[data808]

The merged cells wouldn't cause that. The value in E3 might not be a date? Possibly a text value?

Ok I'll check and let you know. Thanks for the reply.

 

[data808]

The merged cells wouldn't cause that. The value in E3 might not be a date? Possibly a text value?

Ok I tried formatting the cell with 3 formats. Date, Text, and General. Each time I formatted the cell, I made sure the function bar was empty. After changing the format then I would save and exit the file then open it back up and plug your code into the function bar and each time I would get the #VALUE! error.

 

[kevin9999]

OK, I'll need to see your actual data using the XL2BB Add in before I can help you any further. The problem is that the formula works fine for me (even with merged cells - see below) so I can't reproduce your error.

test.xlsm
	C	D	E
2	May
3	5/05/2022		5/05/2022
Sheet2

Cell Formulas
Range	Formula
C2	C2	=IF(MONTH(C3)=MONTH(E3),TEXT(C3,"mmmm"),TEXT(C3,"mmmm")&" - "&TEXT(E3,"mmmm"))

test.xlsm
	C	D	E
2	May - December
3	5/05/2022		5/12/2022
Sheet2

Cell Formulas
Range	Formula
C2	C2	=IF(MONTH(C3)=MONTH(E3),TEXT(C3,"mmmm"),TEXT(C3,"mmmm")&" - "&TEXT(E3,"mmmm"))

 

[data808]

OK, I'll need to see your actual data using the XL2BB Add in before I can help you any further. The problem is that the formula works fine for me (even with merged cells - see below) so I can't reproduce your error.

test.xlsm
	C	D	E
2	May
3	5/05/2022		5/05/2022
Sheet2

Cell Formulas
Range	Formula
C2	C2	=IF(MONTH(C3)=MONTH(E3),TEXT(C3,"mmmm"),TEXT(C3,"mmmm")&" - "&TEXT(E3,"mmmm"))

test.xlsm
	C	D	E
2	May - December
3	5/05/2022		5/12/2022
Sheet2

Cell Formulas
Range	Formula
C2	C2	=IF(MONTH(C3)=MONTH(E3),TEXT(C3,"mmmm"),TEXT(C3,"mmmm")&" - "&TEXT(E3,"mmmm"))

I just tested it on a blank spreadsheet and it works. I also just thought of why it may not be working on my main spreadsheet and it could be because C3 and E3 are not cell values entered by the user. They are pulling the date values from sheet1. C3 and E3 have these in the function bar:

C3 =IF(sheet1!B2<>"",sheet1!B2,"")
E3 =IF(sheet1!Z2<>"",sheet1!Z2,"")

 

[data808]

OK, I'll need to see your actual data using the XL2BB Add in before I can help you any further. The problem is that the formula works fine for me (even with merged cells - see below) so I can't reproduce your error.

test.xlsm
	C	D	E
2	May
3	5/05/2022		5/05/2022
Sheet2

Cell Formulas
Range	Formula
C2	C2	=IF(MONTH(C3)=MONTH(E3),TEXT(C3,"mmmm"),TEXT(C3,"mmmm")&" - "&TEXT(E3,"mmmm"))

test.xlsm
	C	D	E
2	May - December
3	5/05/2022		5/12/2022
Sheet2

Cell Formulas
Range	Formula
C2	C2	=IF(MONTH(C3)=MONTH(E3),TEXT(C3,"mmmm"),TEXT(C3,"mmmm")&" - "&TEXT(E3,"mmmm"))

Did more testing and its because of the IF statement in the function. That's because when the cell on sheet1 is blank then the cell in sheet2 used to have some random January date and so I didn't want that. I wanted it to be blank as well. I also notice that the code you provided does something similar. If the cells C3 and E3 are blank then it will default to "January" in cell C2. So eventually I would like C2 to be blank as well if the other cells are blank that C2 needs to pull from. Would that be possible? Or is that getting too complicated? Sorry for the confusion also thanks for taking the time to figure this one out. This is way beyond me.

 

[kevin9999]

Did more testing and its because of the IF statement in the function. That's because when the cell on sheet1 is blank then the cell in sheet2 used to have some random January date and so I didn't want that. I wanted it to be blank as well. I also notice that the code you provided does something similar. If the cells C3 and E3 are blank then it will default to "January" in cell C2. So eventually I would like C2 to be blank as well if the other cells are blank that C2 needs to pull from. Would that be possible? Or is that getting too complicated? Sorry for the confusion also thanks for taking the time to figure this one out. This is way beyond me.

No worries, leave it with me.

 

[kevin9999]

Try this to start with

=IF(C3="","",IF(MONTH(C3)=MONTH(E3),TEXT(C3,"mmmm"),TEXT(C3,"mmmm")&" - "&TEXT(E3,"mmmm")))

 

[data808]

Try this to start with

=IF(C3="","",IF(MONTH(C3)=MONTH(E3),TEXT(C3,"mmmm"),TEXT(C3,"mmmm")&" - "&TEXT(E3,"mmmm")))

Perfect! No need to start with it. This is going in permanently. Works exactly how I wanted. Thank you very much for all the help @kevin9999.

 

[kevin9999]

Perfect! No need to start with it. This is going in permanently. Works exactly how I wanted. Thank you very much for all the help @kevin9999[/U
[/QUOTE]

[/QUOTE]

Glad we got there in the end :)