Referencing Date problem

[Raz]

This is what I have in the cell where I enter the date and time (Cell A1:A3
(Marged cell) of Sheet1)
2/6/2008 8:00:00 (I need to keep it in this format.)


This is what shows up on the formulla bar when I click on the above cell to
enter date.
06/02/2008 8:00:00 AM

On Cell A1 of Sheet2, I need to have this:
Shut In Date: 06/02/2008 8:00:00 AM
and
I use this formulla in Cell A1, Sheet2 ="Shut In Date: "&'Sheet1'!A1
but its giving me Shut In Date: 39484.333333333

PLEASE HELP

 

[Pete_UK]

Try it this way:

="Shut In Date: "&TEXT('Sheet1'!A1,"dd/mm/yyyy h:mm:ss AM/PM")

It's not clear if you want the month first or the day, so you might
need to swap the dd and the mm around in the format string.

Hope this helps.

Pete

 

[Niek Otten]

="Shut In Date: " & TEXT(Sheet1!A1,"mm/dd/yyyy h:mm:ss AM/PM")

 

[Raz]

Thanks Niek It Worked.

="Shut In Date: " & TEXT(Sheet1!A1,"mm/dd/yyyy h:mm:ss AM/PM")

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

 

[Gord Dibben]

="Shut In Date: "&TEXT(Sheet1!A1,"mm/dd/yyyy hh:mm:ss am/pm")


Gord Dibben MS Excel MVP