Cells containing dates format incorrectly when used in a formula

Question

I have added formulas to an existing Excel spreadsheet to generate emails and it works great except for the dates. One of the cells used in the formulas is formatted to Date but the information in that cell converts back to General in the cells resulting from the formula containing it. You can see in the snip that cell E2 is a date (the entire column is formatted to Date) but the date does not appear as a date in the resulting cells I2 and J2, it appears as the number generated when it is formatted as General instead. I have tried adding DATE(E2) to the formula which results in an error telling me that I’m using too few functions. I am new to Excel and hoping that someone can see my error and tell me how to correct it.

Answer 1

Try the following. If required, edit the date format in the formula to match your required regional date format.

=IF(F2="No","","Action due: " & TEXT(E2,"dd/mm/yyyy"))

Concatenation requires the date to be converted to text in the formula.

Answer 2

That worked perfectly, thank you so much!!!