Excel converts a date to text - how to change back to date

[Pumpk1n]

I have a formula "=sheet1!C2" excel displays 9/10/18. When I update th formula to be "=sheet1!C2 & " - " &Sheet1!D2", EXCEL
displays 43353 - 43357; I want it to display 9/10/18 - 9/17/18. What do I need to add to the formula. Thanks!

 

[Joe4]

That is because dates really are numbers (the number of days since 1/0/1900) with a special format in Excel. Formatting does not carry over in formulas.
So, just format the numbers the same way in your formula using the TEXT function.

[COLOR=#333333]=TEXT(Sheet1!C2,"M/D/YY") & " - " & TEXT(Sheet1!D2,"M/D/YY")[/COLOR]

 

[Pumpk1n]

Thank you!

 

[Joe4]

You are welcome.

 

[Rick Rothstein]

That is because dates really are numbers (the number of days since 1/0/1900) with a special format in Excel. Formatting does not carry over in formulas.
So, just format the numbers the same way in your formula using the TEXT function.

[COLOR=#333333]=TEXT(Sheet1!C2,"M/D/YY") & " - " & TEXT(Sheet1!D2,"M/D/YY")[/COLOR]

Probably a little less clear, but you can eliminate Excel having to perform one concatenation by embedding the space/dash/space delimiter within the first (or second if you would rather) TEXT function call...

=TEXT(Sheet1!C2,"M/D/YY - ")&TEXT(Sheet1!D2,"M/D/YY")

 

[Pumpk1n]

Thank you!