Transpose Holidays in Column to Rows

[mycon73]

HI

I have this list of holidays in table format

01/01/2021 New Year's Day
01/18/2021 Martin Luther King Day
02/15/2021 Presidents Day and Washington's Birthday
05/31/2021 Memorial Day
07/04/2021 Independence Day
09/06/2021 Labor Day
10/11/2021 Columbus Day
11/11/2021 Veterans' Day
11/25/2021 Thanksgiving
12/25/2021 Christmas Day
01/01/2021 New Year's Day
01/17/2021 Martin Luther King Day
02/21/2021 Presidents Day and Washington's Birthday
05/30/2021 Memorial Day
07/04/2021 Independence Day
09/05/2021 Labor Day
10/10/2021 Columbus Day
11/11/2021 Veterans' Day
11/24/2021 Thanksgiving
12/25/2021 Christmas Day


I want to transpose dates only to row format

In Cell, C33 (or any cell), have 01/01/21

When attempting to use: =TEXT(C33&C11&", ","mm/dd/yy")

Not getting desired date format

How to get desired date format?



01/01/21
4419744347,
4419744347, 44381,
4419744347, 44381, 44445,
4419744347, 44381, 44445, 44480,
4419744347, 44381, 44445, 44480, 44511,

[alansidman]

Fast answers need visual help. Please read the yellow banner at the top of this page on how to attach a file.

[GeoffW283]

The first parameter of the text() function needs to be numeric (which a date serial number is). Once you have appended C33&C11 you no longer have a numeric value, it is text at that point so the formatting will not work in the way that you intended.
Instead of:
=TEXT(C33&C11&", ","mm/dd/yy")
Try:
=TEXT(C33, "mm/dd/yy, ")&TEXT(C11, "mm/dd/yy, ")

I'm not certain what end result you want. To reproduce your table that looks like:
01/01/21
4419744347,
4419744347, 44381,
4419744347, 44381, 44445,
4419744347, 44381, 44445, 44480,
4419744347, 44381, 44445, 44480, 44511,

but with intelligible dates then in C33 copied down, try in C33 copied down:
=C32&TEXT(C11, "mm/dd/yy, ")
This yields:
01/01/21,
01/01/21, 01/18/21,
01/01/21, 01/18/21, 02/15/21,
01/01/21, 01/18/21, 02/15/21, 05/31/21,
01/01/21, 01/18/21, 02/15/21, 05/31/21, 07/04/21,


Or are you looking for a list that is a particular subset of the holidays, in which case how is that subset defined? What does your expected end result look like?

If I have misunderstood what you want or if the above is not enough to point you in the right direction then, as Alan says above, please provide a sample workbook and additional details.

[mycon73]

GeoffW283

Thanks!!!

This, =C32&TEXT(C11, "mm/dd/yy, ") worked well!!

[AliGW]

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

Also, if you have not already done so, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of all those who offered help.