Referencing Date problem

  • Thread starter Thread starter Raz
  • Start date Start date
R

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
 
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
 
="Shut In Date: "&TEXT(Sheet1!A1,"mm/dd/yyyy hh:mm:ss am/pm")


Gord Dibben MS Excel MVP
 
Back
Top