Simple question....

[Omakbob]

but I can't figure out the answer. I have the following formula in a cell:

=CONCATENATE("Effective Date: ",SIC!I21)

SIC!I21 is a date, but in the cell with the above formula, it shows up as
39173 when it should read 04/01/07.

How do I get it to change from 39173 to 04/01/07?

Thanks

 

[Dave Peterson]

=CONCATENATE("Effective Date: ",text(SIC!I21,"mm/dd/yyyy")
or just:
="Effective Date: " & text(SIC!I21,"mm/dd/yyyy")

 

[Dave F]

See here for a way: http://www.mrexcel.com/archive/Dates/28708.html

Dave

 

[Omakbob]

Thank you very much!!!

See here for a way: http://www.mrexcel.com/archive/Dates/28708.html

Dave

 

[Omakbob]

Thank you very much!

=CONCATENATE("Effective Date: ",text(SIC!I21,"mm/dd/yyyy")
or just:
="Effective Date: " & text(SIC!I21,"mm/dd/yyyy")

 

[David Biddulph]

=CONCATENATE("Effective Date: ",TEXT(SIC!I21,"mm/dd/yy"))
or alter the format element of the TEXT function to suit.