| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Convert Date to STring
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: http://www.excelforum.com/member.php...o&userid=27819 View this thread: http://www.excelforum.com/showthread...hreadid=512562 |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Convert Date to STring
=TEXT(A1,1)
************ Hope it helps! Anne Troy www.OfficeArticles.com Check out the NEWsgroup stats! Check out: www.ExcelUserConference.com "tonymaguire" wrote in message ... 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: http://www.excelforum.com/member.php...o&userid=27819 View this thread: http://www.excelforum.com/showthread...hreadid=512562 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Convert Date to STring
Either format the receiving cell as date or use this formula to move
the date across: =TEXT(A1;"MM/DD/YYYY") |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Convert Date to STring
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 "tonymaguire" wrote in message ... 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: http://www.excelforum.com/member.php...o&userid=27819 View this thread: http://www.excelforum.com/showthread...hreadid=512562 |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Convert Date to STring
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: =TEXT(A1,1) ************ Hope it helps! Anne Troy www.OfficeArticles.com Check out the NEWsgroup stats! Check out: www.ExcelUserConference.com "tonymaguire" wrote in message ... 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: http://www.excelforum.com/member.php...o&userid=27819 View this thread: http://www.excelforum.com/showthread...hreadid=512562 -- tonymaguire ------------------------------------------------------------------------ tonymaguire's Profile: http://www.excelforum.com/member.php...o&userid=27819 View this thread: http://www.excelforum.com/showthread...hreadid=512562 |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Convert Date to STring
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 Tony flummi Wrote: Either format the receiving cell as date or use this formula to move the date across: =TEXT(A1;"MM/DD/YYYY") -- tonymaguire ------------------------------------------------------------------------ tonymaguire's Profile: http://www.excelforum.com/member.php...o&userid=27819 View this thread: http://www.excelforum.com/showthread...hreadid=512562 |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Convert Date to STring
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: 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 "tonymaguire" wrote in message ... 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: http://www.excelforum.com/member.php...o&userid=27819 View this thread: http://www.excelforum.com/showthread...hreadid=512562 -- tonymaguire ------------------------------------------------------------------------ tonymaguire's Profile: http://www.excelforum.com/member.php...o&userid=27819 View this thread: http://www.excelforum.com/showthread...hreadid=512562 |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Convert Date to STring
Hi 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.2006 12.05.2006 =TEXT(A1;"TT.MM.JJJJ") Try reading the help for the "=TEXT" function to find the valid separators in your system. Hans |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Convert Date to STring
Hans, Thanks for the clue re separators. I changed the format to YYYYMMDD. Then =TEXT(A1;"YYYYMMDD") 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: http://www.excelforum.com/member.php...o&userid=27819 View this thread: http://www.excelforum.com/showthread...hreadid=512562 |
|
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Convert Date to STring
On Wed, 15 Feb 2006 00:40:36 -0600, tonymaguire
wrote: 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. You use the TEXT worksheet function. eg: =TEXT(A1,"YYYYMMDD") --ron |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Cell Date / Currency Convert | Excel Discussion (Misc queries) | |||
| Need help: convert seconds to date and time | Excel Discussion (Misc queries) | |||
| convert Julian date | Excel Worksheet Functions | |||
| How to convert string to a date | Excel Worksheet Functions | |||
| How do I convert a Julian date into a regular date? | Excel Discussion (Misc queries) | |||
Linear Mode
