software

How do I convert a date (25/11/2005) in a date field to the same string
in a General or Text field? Whenever I try Excel converts to the
serial value of the date.--
tonymaguire
------------------------------------------------------------------------
tonymaguire's Profile: www.excelforum.com/member.php...oamp;userid=27819
View this thread: www.excelforum.com/showthread...hreadid=512562=TEXT(A1,1)

************
Hope it helps!
Anne Troy
www.OfficeArticles.com
Check out the NEWsgroup stats!
Check out: www.ExcelUserConference.com

quot;tonymaguirequot; gt;
wrote in message
...
gt;
gt; How do I convert a date (25/11/2005) in a date field to the same string
gt; in a General or Text field? Whenever I try Excel converts to the
gt; serial value of the date.
gt;
gt;
gt; --
gt; tonymaguire
gt; ------------------------------------------------------------------------
gt; tonymaguire's Profile:
gt; www.excelforum.com/member.php...oamp;userid=27819
gt; View this thread: www.excelforum.com/showthread...hreadid=512562
gt;
Either format the receiving cell as date or use this formula to move
the date across: =TEXT(A1;quot;MM/DD/YYYYquot;)Hi!

Not sure what you want to do this for, but.........

When you enter a date in a cell and Excel recognizes it as a date, it
automatically sets that cell format to DATE. The true underlying value of
that cell is the date serial number. So, changing the format to either
GENERAL or TEXT, you end up with the date serial number. (when formatted as
TEXT, then it's a TEXT number)

You could enter the string preceded with an apostrophe: '25/11/2005, but
it's strictly a TEXT entry and can't be used in calculations (easily).

Biff

quot;tonymaguirequot; gt;
wrote in message
...
gt;
gt; How do I convert a date (25/11/2005) in a date field to the same string
gt; in a General or Text field? Whenever I try Excel converts to the
gt; serial value of the date.
gt;
gt;
gt; --
gt; tonymaguire
gt; ------------------------------------------------------------------------
gt; tonymaguire's Profile:
gt; www.excelforum.com/member.php...oamp;userid=27819
gt; View this thread: www.excelforum.com/showthread...hreadid=512562
gt;

Anne,

I assume that A1 refers to the Cell holding the date.
When I enter the suggested formula I get a result of 1.Anne Troy Wrote:
gt; =TEXT(A1,1)
gt;
gt; ************
gt; Hope it helps!
gt; Anne Troy
gt; www.OfficeArticles.com
gt; Check out the NEWsgroup stats!
gt; Check out: www.ExcelUserConference.com
gt;
gt; quot;tonymaguirequot;
gt; gt;
gt; wrote in message
gt; ...
gt; gt;
gt; gt; How do I convert a date (25/11/2005) in a date field to the same
gt; string
gt; gt; in a General or Text field? Whenever I try Excel converts to the
gt; gt; serial value of the date.
gt; gt;
gt; gt;
gt; gt; --
gt; gt; tonymaguire
gt; gt;
gt; ------------------------------------------------------------------------
gt; gt; tonymaguire's Profile:
gt; gt; www.excelforum.com/member.php...oamp;userid=27819
gt; gt; View this thread:
gt; www.excelforum.com/showthread...hreadid=512562
gt; gt;--
tonymaguire
------------------------------------------------------------------------
tonymaguire's Profile: www.excelforum.com/member.php...oamp;userid=27819
View this thread: www.excelforum.com/showthread...hreadid=512562
flummi,

thanks for the input.

I don't want to format the receiving cell as date because the source
cell is formatted as date and that is the problem that I am trying to
solve. I need to achieve the following YYYYMMDD as a text field for
input into a system that does not accept date formats.

Your suggested formula generates an error.

Regards
Tonyflummi Wrote:
gt; Either format the receiving cell as date or use this formula to move
gt; the date across: =TEXT(A1;quot;MM/DD/YYYYquot;)--
tonymaguire
------------------------------------------------------------------------
tonymaguire's Profile: www.excelforum.com/member.php...oamp;userid=27819
View this thread: www.excelforum.com/showthread...hreadid=512562
Biff,

Thanks for response.

Problem is that I have a file with date in date field formatted
DD/MM/YYYY.

I need to convert this to a text format cell YYYYMMDD for input into a
system that requires input in text format.Biff Wrote:
gt; Hi!
gt;
gt; Not sure what you want to do this for, but.........
gt;
gt; When you enter a date in a cell and Excel recognizes it as a date, it
gt; automatically sets that cell format to DATE. The true underlying value
gt; of
gt; that cell is the date serial number. So, changing the format to either
gt; GENERAL or TEXT, you end up with the date serial number. (when
gt; formatted as
gt; TEXT, then it's a TEXT number)
gt;
gt; You could enter the string preceded with an apostrophe: '25/11/2005,
gt; but
gt; it's strictly a TEXT entry and can't be used in calculations (easily).
gt;
gt; Biff
gt;
gt; quot;tonymaguirequot;
gt; gt;
gt; wrote in message
gt; ...
gt; gt;
gt; gt; How do I convert a date (25/11/2005) in a date field to the same
gt; string
gt; gt; in a General or Text field? Whenever I try Excel converts to the
gt; gt; serial value of the date.
gt; gt;
gt; gt;
gt; gt; --
gt; gt; tonymaguire
gt; gt;
gt; ------------------------------------------------------------------------
gt; gt; tonymaguire's Profile:
gt; gt; www.excelforum.com/member.php...oamp;userid=27819
gt; gt; View this thread:
gt; www.excelforum.com/showthread...hreadid=512562
gt; gt;--
tonymaguire
------------------------------------------------------------------------
tonymaguire's Profile: www.excelforum.com/member.php...oamp;userid=27819
View this thread: www.excelforum.com/showthread...hreadid=512562Hi Tony,

It's possibly a matter of your date separators.

This is what I tried here (Germany) and as you see it works:

12.05.200612.05.2006=TEXT(A1;quot;TT.MM.JJJJquot;)

Try reading the help for the quot;=TEXTquot; function to find the valid
separators in your system.

Hans
Hans,

Thanks for the clue re separators.

I changed the format to YYYYMMDD.
Then =TEXT(A1;quot;YYYYMMDDquot;)

That successfully gave me the string in General format, but when I
tried to separate the components using LEFT and RIGHT the cell still
behaved like a date field.

This problem was solved by converting from General To Text.

Thanks for your help.

Tony Maguire
Melbourne Australia--
tonymaguire
------------------------------------------------------------------------
tonymaguire's Profile: www.excelforum.com/member.php...oamp;userid=27819
View this thread: www.excelforum.com/showthread...hreadid=512562On Wed, 15 Feb 2006 00:40:36 -0600, tonymaguire
gt; wrote:

gt;
gt;How do I convert a date (25/11/2005) in a date field to the same string
gt;in a General or Text field? Whenever I try Excel converts to the
gt;serial value of the date.

You use the TEXT worksheet function.

eg: =TEXT(A1,quot;YYYYMMDDquot;)--ron