Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Change format of a date field
I have been given a csv file - on opening in excel the dates are in the
format 25051917, 5051949, 23121979 etc. I have been struggling to get them into the standard date format - in this case 25/05/1917, 05/05/1949, 23/12/1979 etc. Any help appreciated. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Change format of a date field
suppose u have value in cell A1 then put this formula in B1
=IF(LEN(A1)=8,LEFT(A1,2)&"/"&MID(A1,3,2)&"/"&RIGHT(A1,4),LEFT(A1,1) &"/"&MID(A1,2,2)&"/"&RIGHT(A1,4)) On Nov 12, 2:02*pm, Sageman wrote: I have been given a csv file - on opening in excel the dates are in the format 25051917, *5051949, 23121979 *etc. I have been struggling to get them into the standard date format - in this case 25/05/1917, 05/05/1949, 23/12/1979 etc. *Any help appreciated. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Change format of a date field
Brilliant - works a treat - Thanks very much.
"muddan madhu" wrote: suppose u have value in cell A1 then put this formula in B1 =IF(LEN(A1)=8,LEFT(A1,2)&"/"&MID(A1,3,2)&"/"&RIGHT(A1,4),LEFT(A1,1) &"/"&MID(A1,2,2)&"/"&RIGHT(A1,4)) On Nov 12, 2:02 pm, Sageman wrote: I have been given a csv file - on opening in excel the dates are in the format 25051917, 5051949, 23121979 etc. I have been struggling to get them into the standard date format - in this case 25/05/1917, 05/05/1949, 23/12/1979 etc. Any help appreciated. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Change format of a date field
Hi,
You can also try this. Highlight the range and go to Data Text to columns. While on Delimited, click on next twice and then select Date in Column Date format. Select DMY in the drop down and select any blank cell in destination. Click on Finish. This should get the dates in the required format. Please note that this will not give you the correct result for 5051949. To make this work, you will have to precede 5051949 with 05051949. -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "Sageman" wrote in message ... I have been given a csv file - on opening in excel the dates are in the format 25051917, 5051949, 23121979 etc. I have been struggling to get them into the standard date format - in this case 25/05/1917, 05/05/1949, 23/12/1979 etc. Any help appreciated. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Change format of a date field
A slightly different answer from me:
you can use: =IF(LEN(A1)=8,DATE(RIGHT(A1,4),MID(A1,3,2),LEFT(A1 ,2)),DATE(RIGHT(A1,4),MID(A1,2,2),LEFT(A1,1))) Here if the DATE FORMAT that you derived from a CSV file and your EXCEL program is different then just extracting date using left right and mid functions can give errors (like date of mm/dd/yyyy type in place of dd/mm/yyyy). -- Kind Regards, Satti Charvak Only an Excel Enthusiast Noida, India "Sageman" wrote: Brilliant - works a treat - Thanks very much. "muddan madhu" wrote: suppose u have value in cell A1 then put this formula in B1 =IF(LEN(A1)=8,LEFT(A1,2)&"/"&MID(A1,3,2)&"/"&RIGHT(A1,4),LEFT(A1,1) &"/"&MID(A1,2,2)&"/"&RIGHT(A1,4)) On Nov 12, 2:02 pm, Sageman wrote: I have been given a csv file - on opening in excel the dates are in the format 25051917, 5051949, 23121979 etc. I have been struggling to get them into the standard date format - in this case 25/05/1917, 05/05/1949, 23/12/1979 etc. Any help appreciated. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Change format of a date field
This is a little more compact...
=TEXT(A1,"00\/00\/0000") -- Rick (MVP - Excel) "Sageman" wrote in message ... Brilliant - works a treat - Thanks very much. "muddan madhu" wrote: suppose u have value in cell A1 then put this formula in B1 =IF(LEN(A1)=8,LEFT(A1,2)&"/"&MID(A1,3,2)&"/"&RIGHT(A1,4),LEFT(A1,1) &"/"&MID(A1,2,2)&"/"&RIGHT(A1,4)) On Nov 12, 2:02 pm, Sageman wrote: I have been given a csv file - on opening in excel the dates are in the format 25051917, 5051949, 23121979 etc. I have been struggling to get them into the standard date format - in this case 25/05/1917, 05/05/1949, 23/12/1979 etc. Any help appreciated. |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Change format of a date field
Thanks guys for all your help - very useful and some ideas to use with other
problems! "Ashish Mathur" wrote: Hi, You can also try this. Highlight the range and go to Data Text to columns. While on Delimited, click on next twice and then select Date in Column Date format. Select DMY in the drop down and select any blank cell in destination. Click on Finish. This should get the dates in the required format. Please note that this will not give you the correct result for 5051949. To make this work, you will have to precede 5051949 with 05051949. -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "Sageman" wrote in message ... I have been given a csv file - on opening in excel the dates are in the format 25051917, 5051949, 23121979 etc. I have been struggling to get them into the standard date format - in this case 25/05/1917, 05/05/1949, 23/12/1979 etc. Any help appreciated. |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Change format of a date field
You are welcome.
-- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "Sageman" wrote in message ... Thanks guys for all your help - very useful and some ideas to use with other problems! "Ashish Mathur" wrote: Hi, You can also try this. Highlight the range and go to Data Text to columns. While on Delimited, click on next twice and then select Date in Column Date format. Select DMY in the drop down and select any blank cell in destination. Click on Finish. This should get the dates in the required format. Please note that this will not give you the correct result for 5051949. To make this work, you will have to precede 5051949 with 05051949. -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "Sageman" wrote in message ... I have been given a csv file - on opening in excel the dates are in the format 25051917, 5051949, 23121979 etc. I have been struggling to get them into the standard date format - in this case 25/05/1917, 05/05/1949, 23/12/1979 etc. Any help appreciated. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Format date and time from one contiguous field | Excel Discussion (Misc queries) | |||
Help: format records (color) at each change in continious field | Excel Discussion (Misc queries) | |||
Update date field upon cell range change | Excel Worksheet Functions | |||
How do I format date field in footer? | New Users to Excel | |||
How can I format a cell so date field only displays the Month? | Excel Discussion (Misc queries) |