Basically i have a sheet that has a lot of columns on it and i update it frequently each day and i want to take 4 specific columns and put it on a a separate sheet to send to my boss at the end of each week. I tried using the CONCAT formula to auto copy each cell automatically instead of copying each column and pasting it each week. This worked great until i tested it with a column with dates in it, for example 10/20/2023. when i did this it showed some weird number like 45219. i also tried the TEXT formula and using the MM/DD/YYYY format, but it gave a date to the blank cells, for example 01/00/1900. I want it to show nothing when its blank. what formula should i do. Ill add a picture of basically what i want it to look like.
so say michele is the only one thats had her eval signed and i put the date on the other sheet on this sheet it should look like this
You can use a formula in those (column B) cells. Assuming the source for B3 is D5 on Sheet 7, its formula would be:
=IF( ISNUMBER('Sheet 7'!D5), 'Sheet 7'!D5, "" )(Most of those spaces are optional, but not ones inside a worksheet name; I included the others for improved readability.)
That "weird number" is Excel's internal representation of a date, specifically, the number of days since Jan 0, 1900 (Dec 31, 1899) ... not exactly right because of a design flaw, and it's different with an Excel for Mac workbook, but you need not worry about that. So alternatively you can just apply a custom format to your date column(s) that hides any zeros (and negative numbers, which should not occur) that you generate; for example, m/d/yyyy;;;@
You could use that same custom format in your TEXT function. But the result of a TEXT function is text, which makes for extra work if you boss wants to do date arithmetic or manipulation.
More info on custom formats: Custom Excel number format
For such sample
If to assume you have only texts and dates in the columns that could be
=TEXTJOIN(", ", 0, TEXT(B3:E3, "mm/dd/yyyy;;") )if numbers as well
=B7 & ", " & TEXT(C7, "mm/dd/yyyy;;") & ", " & D7 & ", " & E7