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

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


Omakbob wrote:

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

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

Dave
--
A hint to posters: Specific, detailed questions are more likely to be
answered than questions that provide no detail about your problem.


"Omakbob" wrote:

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

Thank you very much!!!

"Dave F" wrote:

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

Dave
--
A hint to posters: Specific, detailed questions are more likely to be
answered than questions that provide no detail about your problem.


"Omakbob" wrote:

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

Thank you very much!

"Dave Peterson" wrote:

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


Omakbob wrote:

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/yy"))
or alter the format element of the TEXT function to suit.
--
David Biddulph

"Omakbob" wrote in message
...
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