Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Adam
 
Posts: n/a
Default How do I extract a date as text not the 1900 reference number

I know how to format a date within a cell, but when I reference that cell in
a line of text I am constructing, e.g. ="Since "&A1", XXX has changed by
"&B2&"%" ..., the date comes across in the 1900 number format, e.g. it
displays as "Since 38414 ..." instead of saying "Since 3 March 2005 ..."

How do I get it to give me the date as text rather than Excel's background
reference number?
  #2   Report Post  
Ron Rosenfeld
 
Posts: n/a
Default

On Tue, 22 Mar 2005 19:15:01 -0800, "Adam"
wrote:

I know how to format a date within a cell, but when I reference that cell in
a line of text I am constructing, e.g. ="Since "&A1", XXX has changed by
"&B2&"%" ..., the date comes across in the 1900 number format, e.g. it
displays as "Since 38414 ..." instead of saying "Since 3 March 2005 ..."

How do I get it to give me the date as text rather than Excel's background
reference number?


=TEXT(A1,"d mmmm yyyy")


--ron
  #3   Report Post  
Adam
 
Posts: n/a
Default

Perfect, thanks.

"Ron Rosenfeld" wrote:

On Tue, 22 Mar 2005 19:15:01 -0800, "Adam"
wrote:

I know how to format a date within a cell, but when I reference that cell in
a line of text I am constructing, e.g. ="Since "&A1", XXX has changed by
"&B2&"%" ..., the date comes across in the 1900 number format, e.g. it
displays as "Since 38414 ..." instead of saying "Since 3 March 2005 ..."

How do I get it to give me the date as text rather than Excel's background
reference number?


=TEXT(A1,"d mmmm yyyy")


--ron

  #4   Report Post  
Ron Rosenfeld
 
Posts: n/a
Default

On Wed, 23 Mar 2005 03:25:04 -0800, "Adam"
wrote:

Perfect, thanks.


You're welcome. You might also use the same technique in formatting your
percent number, depending on how the original is stored.


--ron
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
pasting data from a website changes text to date stebro Excel Discussion (Misc queries) 8 August 12th 06 08:39 PM
Excel enters date as a text format Kane Excel Discussion (Misc queries) 3 March 22nd 05 10:20 PM
How do I convert a number formated as a date to text in Excel? BrotherNov Excel Discussion (Misc queries) 5 March 2nd 05 04:51 PM
extract numbers, convert to date gkaspen Excel Discussion (Misc queries) 7 March 2nd 05 03:31 AM
EXTRACT TEXT FROM A DATE Ronbo Excel Worksheet Functions 5 February 1st 05 08:39 AM


All times are GMT +1. The time now is 06:27 AM.

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

About Us

"It's about Microsoft Excel"