If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 |
#4
|
|||
|
|||
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 |
#5
|
|||
|
|||
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 |
Thread Tools | |
Display Modes | |
|
|