Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Need help with converting CUSTOM format/TEXT format to DATE format
A column in an excel file was formatted in CUSTOM format like 00-00-0000.
Whenever I changed the format to DATE (ex. 01-01-1901), it is changing to 06/27/4670 which is wrong. I changed the format to TEXT and it results to 1011901. I need a date format as mm/dd/yyyy so I can import this excel file with the correct date format. Please help. Thanks. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Need help with converting CUSTOM format/TEXT format to DATE format
Hi,
what information do you have in that cell, if you have a date when selecting another format it should work, check the number in the cell before formating it, the number give below when formating as date it give you 06/27/4670 so something is wrong with the number you are importing into that cell "Deo Cleto" wrote: A column in an excel file was formatted in CUSTOM format like 00-00-0000. Whenever I changed the format to DATE (ex. 01-01-1901), it is changing to 06/27/4670 which is wrong. I changed the format to TEXT and it results to 1011901. I need a date format as mm/dd/yyyy so I can import this excel file with the correct date format. Please help. Thanks. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Need help with converting CUSTOM format/TEXT format to DATE fo
what information do you have in that cell ANS: Originally it was entered 01-01-1901, in CUSTOM format as 00-00-0000 so I changed it to DATE format but the system changed it to 06/27/4670. Again, from the CUSTOM format 01-01-1901, I changed it to TEXT format and it changed to 1011901. I really need to changed it to DATE format as mm/dd/yyyy, is there any worksheet function that can convert CUSTOM or TEXT formt to DATE format. Thanks for your quick response. "Eduardo" wrote: Hi, what information do you have in that cell, if you have a date when selecting another format it should work, check the number in the cell before formating it, the number give below when formating as date it give you 06/27/4670 so something is wrong with the number you are importing into that cell "Deo Cleto" wrote: A column in an excel file was formatted in CUSTOM format like 00-00-0000. Whenever I changed the format to DATE (ex. 01-01-1901), it is changing to 06/27/4670 which is wrong. I changed the format to TEXT and it results to 1011901. I need a date format as mm/dd/yyyy so I can import this excel file with the correct date format. Please help. Thanks. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Need help with converting CUSTOM format/TEXT format to DATE fo
Hi Deo,
try =DATE(VALUE(LEFT(A1,1)),VALUE(MID(A1,2,2)),VALUE(R IGHT(A1,2))) I assume that the number is in cell A1 change it to fit your needs if this helps please click yes, thanks "Deo Cleto" wrote: what information do you have in that cell ANS: Originally it was entered 01-01-1901, in CUSTOM format as 00-00-0000 so I changed it to DATE format but the system changed it to 06/27/4670. Again, from the CUSTOM format 01-01-1901, I changed it to TEXT format and it changed to 1011901. I really need to changed it to DATE format as mm/dd/yyyy, is there any worksheet function that can convert CUSTOM or TEXT formt to DATE format. Thanks for your quick response. "Eduardo" wrote: Hi, what information do you have in that cell, if you have a date when selecting another format it should work, check the number in the cell before formating it, the number give below when formating as date it give you 06/27/4670 so something is wrong with the number you are importing into that cell "Deo Cleto" wrote: A column in an excel file was formatted in CUSTOM format like 00-00-0000. Whenever I changed the format to DATE (ex. 01-01-1901), it is changing to 06/27/4670 which is wrong. I changed the format to TEXT and it results to 1011901. I need a date format as mm/dd/yyyy so I can import this excel file with the correct date format. Please help. Thanks. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Need help with converting CUSTOM format/TEXT format to DATE format
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Need help with converting CUSTOM format/TEXT format to DATE fo
Can you get rid of the timestamps, when I imported this file to TOAD it
included the timestamp: Insert into DJIMENEZ.TEMP_INSD_BDAY (POLICY_SEARCH_NBR, LAST_NAME, FIRST_NAME, MIDDLE_NAME, SUFFIX, SSN, ADDRESS, CITY, STATE, ZIP_CODE, PO_BOX_FLAG, DOB, DATE_OF_BIRTH) Values ('3-HOC-1-1144198 ', 'OLSZEWSKI ', 'JOYCE ', ' ', ' ', ' ', '1502 STOCKBRIDGE DRIVE ', 'SAN JOSE ', 'CA ', '95130', '0', '1011901', TO_DATE('01/01/1901 00:00:00', 'MM/DD/YYYY HH24:MI:SS')); "Eduardo" wrote: Hi Deo, try =DATE(VALUE(LEFT(A1,1)),VALUE(MID(A1,2,2)),VALUE(R IGHT(A1,2))) I assume that the number is in cell A1 change it to fit your needs if this helps please click yes, thanks "Deo Cleto" wrote: what information do you have in that cell ANS: Originally it was entered 01-01-1901, in CUSTOM format as 00-00-0000 so I changed it to DATE format but the system changed it to 06/27/4670. Again, from the CUSTOM format 01-01-1901, I changed it to TEXT format and it changed to 1011901. I really need to changed it to DATE format as mm/dd/yyyy, is there any worksheet function that can convert CUSTOM or TEXT formt to DATE format. Thanks for your quick response. "Eduardo" wrote: Hi, what information do you have in that cell, if you have a date when selecting another format it should work, check the number in the cell before formating it, the number give below when formating as date it give you 06/27/4670 so something is wrong with the number you are importing into that cell "Deo Cleto" wrote: A column in an excel file was formatted in CUSTOM format like 00-00-0000. Whenever I changed the format to DATE (ex. 01-01-1901), it is changing to 06/27/4670 which is wrong. I changed the format to TEXT and it results to 1011901. I need a date format as mm/dd/yyyy so I can import this excel file with the correct date format. Please help. Thanks. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Converting a date to text..Format question | Excel Discussion (Misc queries) | |||
Converting general text format to date | Excel Discussion (Misc queries) | |||
Converting date into text format | Excel Discussion (Misc queries) | |||
Converting number or text to a Date Format | New Users to Excel | |||
Excel keeps converting text to date format | Excel Discussion (Misc queries) |