A Microsoft Office (Excel, Word) forum. OfficeFrustration

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.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » General Discussion
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Referencing Date problem



 
 
Thread Tools Display Modes
  #1  
Old November 18th, 2009, 07:36 PM posted to microsoft.public.excel.misc
Raz
external usenet poster
 
Posts: 143
Default 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  
Old November 18th, 2009, 07:48 PM posted to microsoft.public.excel.misc
Pete_UK
external usenet poster
 
Posts: 8,780
Default 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  
Old November 18th, 2009, 07:52 PM posted to microsoft.public.excel.misc
Niek Otten
external usenet poster
 
Posts: 2,533
Default 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  
Old November 18th, 2009, 07:58 PM posted to microsoft.public.excel.misc
Raz
external usenet poster
 
Posts: 143
Default 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  
Old November 18th, 2009, 08:59 PM posted to microsoft.public.excel.misc
Gord Dibben
external usenet poster
 
Posts: 20,252
Default 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

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 10:52 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.