Referencing Date problem
 

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

Referencing Date problem
 

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

On Nov 18, 6:36*pm, Raz wrote:
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

Referencing Date problem
 

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

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"Raz" wrote in message
...

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

Referencing Date problem
 

Thanks Niek It Worked. :)

"Niek Otten" wrote:

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

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"Raz" wrote in message
...

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

Referencing Date problem
 

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


Gord Dibben MS Excel MVP

On Wed, 18 Nov 2009 10:36:01 -0800, Raz
wrote:


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