Combining columns with dates

[Hiker]

I'm trying to combine two columns into a third called membership_status_combined. I'm also working with two formats, one renewal column is blank for life and honorary life members and the other populates the column with 1/1/2050. I'd like both to be blank, so I'll most like need a formula for each.

I was able to concatenate (=CONCATENATE(B2,","," ",TEXT(C2,"DD/MM/YYYY")) in the first group of columns. The membership_level with renewal_date combined column looked fine but got an oddball date of 0/1/1900 after life and honorary life when the renewal date column was left blank.

The xl2bb example is what I want it to look like, no formulas were used.

Book1.xlsx
	A	B	C	D	E	F	G
1	membership_status_combined	membership_level	renewal_date		membership_status_combined	membership_level	renewal_date
2	1 Year, 11/9/2021	1 Year	11/09/2021		1 Year, 11/9/2021	1 Year	11/09/2021
3	Honorary Life Member	Honorary Life Member			Honorary Life Member	Honorary Life Member	01/01/2050
4	Life Member	Life Member			Life Member	Life Member	01/01/2050
5	2 Years, 5/17/2022	2 Years	05/17/2022		2 Years, 5/17/2022	2 Years	05/17/2022
6	1 Year Recurring, 5/16/2022	1 Year Recurring	05/16/2022		1 Year Recurring, 5/16/2022	1 Year Recurring	05/16/2022
Sheet1

 

[Joe4]

Assuming that the renewal_date column is text (because it is left-justified), I believe this formula for A2 should be able to handle either format:

=IF(OR(C2="",C2="01/01/2050"),B2,B2 & ", " & C2)

 

[Hiker]

That halfway worked. Here's the problem I run into.

Book1.xlsx
	A	B	C	D	E	F	G
1	membership_status_combined	membership_level	renewal_date		membership_status_combined	membership_level	renewal_date
2	1 Year, 44509	1 Year	11/09/2021		1 Year, 44509	1 Year	11/09/2021
3	Honorary Life Member	Honorary Life Member			Honorary Life Member	Honorary Life Member	01/01/2050
4	Life Member	Life Member			Life Member	Life Member	01/01/2050
5	2 Years, 44698	2 Years	05/17/2022		2 Years, 44698	2 Years	05/17/2022
6	1 Year Recurring, 44697	1 Year Recurring	05/16/2022		1 Year Recurring, 44697	1 Year Recurring	05/16/2022
Sheet1

Cell Formulas
Range	Formula
A2:A6	A2	=IF(OR(C2="",C2="01/01/2050"),B2,B2 & ", " & C2)
E2:E6	E2	=IF(OR(C2="",C2="01/01/2050"),B2,B2 & ", " & C2)

 

[Rick Rothstein]

Replace the last C2 (the one you are concatenating) to this...

TEXT(C2,"m/d/yyyy")

 

[Hiker]

Replace the last C2 (the one you are concatenating) to this...

TEXT(C2,"m/d/yyyy")

That's great!

It would have taken me forever to figure it out.

Thanks Rick and Joe!

 

[Joe4]

Glad you got it figured out.

It looks like my assumption that your dates were text (because they were left justified) was not correct.
Either it is just the tool that you used to post the image, or you intentionally made them left justified (because by default, dates are right-justified when enter them into cells that do not have explicit justification set).

And Excel sees dates just as numbers (the number of days since 1/0/1900), so that is the number you were seeing.
So converting it to text and applying the date format like Rick showed is how you change that.