Date changing to number when using formula

[jwoww]

I'm using the concat formula and among the cells it is selecting, some contain dates. when it transfers to the new cell instead of showing the dates it changes to a number and i have to go in and manually adjust, negating me using a formula to join various cells together.

An example of my formula is
=CONCAT(E165&" "&F165&" "&G165&". Marries "&J165&" on "&O165&". Is the first child of "&U165)
where the following cells equate to a date
F165
G165
O165

is there a way to fix this?

 

[Alex Blakenburg]

You will need to apply a date format to your dates.
In Australia we use "dd/mm/yyyy", in the below change that to what you need in your region, it will flow through to the 3 values (cols F,G & O)

=LET(dtFmt,"dd/mm/yyyy",
CONCAT(E165&" "&TEXT(F165,dtFmt)&" "&TEXT(G165,dtFmt)&". Marries "&J165&" on "&TEXT(O165,dtFmt)&". Is the first child of "&U165))

 

[HighAndWilder]

I'm using the concat formula and among the cells it is selecting, some contain dates. when it transfers to the new cell instead of showing the dates it changes to a number and i have to go in and manually adjust, negating me using a formula to join various cells together.

An example of my formula is
=CONCAT(E165&" "&F165&" "&G165&". Marries "&J165&" on "&O165&". Is the first child of "&U165)
where the following cells equate to a date
F165
G165
O165

is there a way to fix this?

It's not changing to a number, dates are stored as numbers.

It is the number of days since 01/01/1900

Wrap each cell reference in the TEXT function.

e.g. TEXT(C7,"dd/mm/yyyy")

 

[Alex Blakenburg]

You will need to apply a date format to your dates.
In Australia we use "dd/mm/yyyy", in the below change that to what you need in your region, it will flow through to the 3 values (cols F,G & O)

Also since you are using "&" instead of a comma, the concat function isn't adding any value, so it can be simplified to:

=LET(dtFmt,"dd/mm/yyyy",
E165&" "&TEXT(F165,dtFmt)&" "&TEXT(G165,dtFmt)&". Marries "&J165&" on "&TEXT(O165,dtFmt)&". Is the first child of "&U165)