formula works in only some cells

Megan F 40 Reputation points
2026-07-28T14:24:51.07+00:00

I am using the following formula to pull multiple dates into one cell from other cells, but there will not always be data. The IF function is already worked into the formula so if there is no data that space will be blank, but it is only working in some cells and not others. I verified all the cells are formatted the same way - they are all general. The exact same formula is in every cell, but some cells are blank and the others are showing 1900-01-01 where there is no data. The only difference in the formula for each cell is the column/row number it's pulling from. All the pull data is also formatted the same way, and I have my preferences set up to not show zeroes where there is no data. Can you please assist?

=TEXTJOIN(" / ", TRUE, MAP(CHOOSECOLS('MASTER FOF'!$H$175:$R$175, 1, 3, 5, 7, 9, 11), LAMBDA(cell, IF(cell="", "", TEXT(cell, "YYYY-MM-DD")))))

Microsoft 365 and Office | Excel | For business | Windows
0 comments
Answer accepted by question author
Marcin Policht 102.3K Reputation points MVP Volunteer Moderator
2026-07-28T14:43:07.3066667+00:00

The formula itself is valid, but the 1900-01-01 result indicates that one or more of the referenced cells contains the numeric value 0 rather than being truly blank. TEXT(0,"YYYY-MM-DD") converts the serial date 0 to 1900-01-01 (or the equivalent start of Excel's date system), so IF(cell="","",...) does not catch it because 0 is not an empty string.

To handle both blank cells and zero values, try modifying the formula to test for both conditions:

=TEXTJOIN(" / ", TRUE, MAP(CHOOSECOLS('MASTER FOF'!$H$175:$R$175,1,3,5,7,9,11), LAMBDA(cell, IF(OR(cell="",cell=0),"",TEXT(cell,"yyyy-mm-dd")))))

If only some cells exhibit this behavior, those source cells are likely returning 0 from a formula rather than "". Check the referenced cells directly by selecting one and pressing F2 to see whether it contains a formula that returns 0. If so, changing the source formula to return "" instead of 0 should also resolve the issue.

If the above response helps answer your question, remember to "Accept Answer" so that others in the community facing similar issues can easily find the solution. Your contribution is highly appreciated.

hth

Marcin

Was this answer helpful?

1 person found this answer helpful.

0 additional answers

Sort by: Most helpful
Most helpful Newest Oldest

Your answer

Answers can be marked as 'Accepted' by the question author and 'Recommended' by moderators, which helps users know the answer solved the author's problem.