Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Change date to number
Client has dates with custom format yyyymmdd. She needs the date to show in
that format but not be a date anymore. For example, the custom format displays the date as 20061006 but she needs this value to display exactly like this but no longer be a date. Everything I've tried keeps reading the value as the serial number rather than literally what is displayed. I've tried the text function, left and mid and right, and text to columns. Any other suggestions?? -- maryj |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Change date to number
On Fri, 6 Oct 2006 09:59:02 -0700, maryj
wrote: Client has dates with custom format yyyymmdd. She needs the date to show in that format but not be a date anymore. For example, the custom format displays the date as 20061006 but she needs this value to display exactly like this but no longer be a date. Everything I've tried keeps reading the value as the serial number rather than literally what is displayed. I've tried the text function, left and mid and right, and text to columns. Any other suggestions?? Since the contents is an actual date, perhaps this: =YEAR(A1)*10^4+MONTH(A1)*100+DAY(A1) --ron |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Change date to number
maryj,
the following formula will produce, in a separate cell, the same display as in A2, only it is now text: =TEXT(A2,"yyyymmdd") If you want to use a column for further data entry, in which your entry will not be interpreted as a date, format the cells as Text (Format|Cells...|Number tab) HTH Kostis Vezerides |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Change date to number
Try this
=CONCATENATE(YEAR(A1),TEXT(MONTH(A1),"00"),TEXT(DA Y(A1),"00")) It will convert the date into text and keep the same format Alok "maryj" wrote: Client has dates with custom format yyyymmdd. She needs the date to show in that format but not be a date anymore. For example, the custom format displays the date as 20061006 but she needs this value to display exactly like this but no longer be a date. Everything I've tried keeps reading the value as the serial number rather than literally what is displayed. I've tried the text function, left and mid and right, and text to columns. Any other suggestions?? -- maryj |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Change date to number
The TEXT function should work. Did you try it like this:
=TEXT(A1,"yyyymmdd") HTH, Elkar "maryj" wrote: Client has dates with custom format yyyymmdd. She needs the date to show in that format but not be a date anymore. For example, the custom format displays the date as 20061006 but she needs this value to display exactly like this but no longer be a date. Everything I've tried keeps reading the value as the serial number rather than literally what is displayed. I've tried the text function, left and mid and right, and text to columns. Any other suggestions?? -- maryj |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Change date to number
Thank you!!! Forgot about setting the format in the text function like that!
-- maryj "vezerid" wrote: maryj, the following formula will produce, in a separate cell, the same display as in A2, only it is now text: =TEXT(A2,"yyyymmdd") If you want to use a column for further data entry, in which your entry will not be interpreted as a date, format the cells as Text (Format|Cells...|Number tab) HTH Kostis Vezerides |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Changing a Number in a Column Using Arrays | Excel Worksheet Functions | |||
Excel: I enter date and format for date, but shows as number | Excel Discussion (Misc queries) | |||
I Need a formula to evaluate a cell with + or - values | Excel Worksheet Functions | |||
How do I change the category axis number formatting in a Pivot Ch. | Charts and Charting in Excel | |||
how do i make a date change automatically if i change one before . | Excel Discussion (Misc queries) |