Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
reformat cells for date
I have a csv file that contains a column with dates in it. Unfortunately the
column has the information entered as DMMYY for 5/12/08 or DDMMYY for 20/12/08. So for days 1st to 9th there is no 0 at the front end. I need to be able to change this coloumn so it reads DD/MM/YYYY so I can import it into another programme. Can any help? Many thanks |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
reformat cells for date
Select all the cells and then do a Format - Cells
Pick the custom format and choose DD/MM/YYYY "Wackyracer" wrote: I have a csv file that contains a column with dates in it. Unfortunately the column has the information entered as DMMYY for 5/12/08 or DDMMYY for 20/12/08. So for days 1st to 9th there is no 0 at the front end. I need to be able to change this coloumn so it reads DD/MM/YYYY so I can import it into another programme. Can any help? Many thanks |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
reformat cells for date
Unfortunately that does not work 10408 (01/04/08) comes out as 29/06/28.
"Dennis" wrote: Select all the cells and then do a Format - Cells Pick the custom format and choose DD/MM/YYYY "Wackyracer" wrote: I have a csv file that contains a column with dates in it. Unfortunately the column has the information entered as DMMYY for 5/12/08 or DDMMYY for 20/12/08. So for days 1st to 9th there is no 0 at the front end. I need to be able to change this coloumn so it reads DD/MM/YYYY so I can import it into another programme. Can any help? Many thanks |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
reformat cells for date
One option =DATE(2000+RIGHT(A1,2),MID(A1,LEN(A1)-3,2),LEFT(A1,LEN(A1)-4))
Another option, if your Windows Regional Settings specify d/m/y, is =--TEXT(A1,"00\/00\/00") Another thing worth checking is whether your CSV file actually has that leading zero missing. If the leading zero is there but the CSV has been opened with Excel, by default Excel would treat it as a number and not display the leading zero. If so, read the CSV data into Excel using the Text Import Wizard and specify the column as text. -- David Biddulph "Wackyracer" wrote in message ... I have a csv file that contains a column with dates in it. Unfortunately the column has the information entered as DMMYY for 5/12/08 or DDMMYY for 20/12/08. So for days 1st to 9th there is no 0 at the front end. I need to be able to change this coloumn so it reads DD/MM/YYYY so I can import it into another programme. Can any help? Many thanks |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
reformat cells for date
Select the column with the "dates"
Data|Text to columns (in xl2003 menus) Fixed width, but remove any lines that excel guessed. Choose Date and dmy and drop the data right back into its original location. Then format that column the way you like. Wackyracer wrote: I have a csv file that contains a column with dates in it. Unfortunately the column has the information entered as DMMYY for 5/12/08 or DDMMYY for 20/12/08. So for days 1st to 9th there is no 0 at the front end. I need to be able to change this coloumn so it reads DD/MM/YYYY so I can import it into another programme. Can any help? Many thanks -- Dave Peterson |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
reformat cells for date
So the cells are actually numbers or text that represent the date ?
Assuming it is only the the days that are the problem and months 1-9 are 01-09 then you can create a new column with the formula =RIGHT("0" & A1,6) "Wackyracer" wrote: Unfortunately that does not work 10408 (01/04/08) comes out as 29/06/28. "Dennis" wrote: Select all the cells and then do a Format - Cells Pick the custom format and choose DD/MM/YYYY "Wackyracer" wrote: I have a csv file that contains a column with dates in it. Unfortunately the column has the information entered as DMMYY for 5/12/08 or DDMMYY for 20/12/08. So for days 1st to 9th there is no 0 at the front end. I need to be able to change this coloumn so it reads DD/MM/YYYY so I can import it into another programme. Can any help? Many thanks |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
reformat cells for date
I had wondered about that method, but for me that doesn't work with 51208,
though it does work with 201208. -- David Biddulph "Dave Peterson" wrote in message ... Select the column with the "dates" Data|Text to columns (in xl2003 menus) Fixed width, but remove any lines that excel guessed. Choose Date and dmy and drop the data right back into its original location. Then format that column the way you like. Wackyracer wrote: I have a csv file that contains a column with dates in it. Unfortunately the column has the information entered as DMMYY for 5/12/08 or DDMMYY for 20/12/08. So for days 1st to 9th there is no 0 at the front end. I need to be able to change this coloumn so it reads DD/MM/YYYY so I can import it into another programme. Can any help? Many thanks -- Dave Peterson |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
reformat cells for date
It worked for me in xl2003--as long as the year was only two digits.
If I used 4 digit years, then it didn't work right. This: 51208 61208 71208 81208 91208 101208 111208 121208 converted to: 05/12/2008 06/12/2008 07/12/2008 08/12/2008 09/12/2008 10/12/2008 11/12/2008 12/12/2008 And this: 50308 60308 70308 80308 90308 100308 110308 120308 Converted to: 05/03/2008 06/03/2008 07/03/2008 08/03/2008 09/03/2008 10/03/2008 11/03/2008 12/03/2008 David Biddulph wrote: I had wondered about that method, but for me that doesn't work with 51208, though it does work with 201208. -- David Biddulph "Dave Peterson" wrote in message ... Select the column with the "dates" Data|Text to columns (in xl2003 menus) Fixed width, but remove any lines that excel guessed. Choose Date and dmy and drop the data right back into its original location. Then format that column the way you like. Wackyracer wrote: I have a csv file that contains a column with dates in it. Unfortunately the column has the information entered as DMMYY for 5/12/08 or DDMMYY for 20/12/08. So for days 1st to 9th there is no 0 at the front end. I need to be able to change this coloumn so it reads DD/MM/YYYY so I can import it into another programme. Can any help? Many thanks -- Dave Peterson -- Dave Peterson |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
reformat cells for date
Perhaps it's dependent on Windows Regional Options?
-- David Biddulph "Dave Peterson" wrote in message ... It worked for me in xl2003--as long as the year was only two digits. If I used 4 digit years, then it didn't work right. This: 51208 61208 71208 81208 91208 101208 111208 121208 converted to: 05/12/2008 06/12/2008 07/12/2008 08/12/2008 09/12/2008 10/12/2008 11/12/2008 12/12/2008 And this: 50308 60308 70308 80308 90308 100308 110308 120308 Converted to: 05/03/2008 06/03/2008 07/03/2008 08/03/2008 09/03/2008 10/03/2008 11/03/2008 12/03/2008 David Biddulph wrote: I had wondered about that method, but for me that doesn't work with 51208, though it does work with 201208. -- David Biddulph "Dave Peterson" wrote in message ... Select the column with the "dates" Data|Text to columns (in xl2003 menus) Fixed width, but remove any lines that excel guessed. Choose Date and dmy and drop the data right back into its original location. Then format that column the way you like. Wackyracer wrote: I have a csv file that contains a column with dates in it. Unfortunately the column has the information entered as DMMYY for 5/12/08 or DDMMYY for 20/12/08. So for days 1st to 9th there is no 0 at the front end. I need to be able to change this coloumn so it reads DD/MM/YYYY so I can import it into another programme. Can any help? Many thanks -- Dave Peterson -- Dave Peterson |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
reformat cells for date
I don't know. My short date format is: mm/dd/yyyy (4 digit year)
I've had trouble with this techique before, but I closed excel and reopened it and it continued to work. When I've had trouble, I'll use the helper column and a formula like: =text(a1,"000000") convert to values and use data|text to columns. David Biddulph wrote: Perhaps it's dependent on Windows Regional Options? -- David Biddulph "Dave Peterson" wrote in message ... It worked for me in xl2003--as long as the year was only two digits. If I used 4 digit years, then it didn't work right. This: 51208 61208 71208 81208 91208 101208 111208 121208 converted to: 05/12/2008 06/12/2008 07/12/2008 08/12/2008 09/12/2008 10/12/2008 11/12/2008 12/12/2008 And this: 50308 60308 70308 80308 90308 100308 110308 120308 Converted to: 05/03/2008 06/03/2008 07/03/2008 08/03/2008 09/03/2008 10/03/2008 11/03/2008 12/03/2008 David Biddulph wrote: I had wondered about that method, but for me that doesn't work with 51208, though it does work with 201208. -- David Biddulph "Dave Peterson" wrote in message ... Select the column with the "dates" Data|Text to columns (in xl2003 menus) Fixed width, but remove any lines that excel guessed. Choose Date and dmy and drop the data right back into its original location. Then format that column the way you like. Wackyracer wrote: I have a csv file that contains a column with dates in it. Unfortunately the column has the information entered as DMMYY for 5/12/08 or DDMMYY for 20/12/08. So for days 1st to 9th there is no 0 at the front end. I need to be able to change this coloumn so it reads DD/MM/YYYY so I can import it into another programme. Can any help? Many thanks -- Dave Peterson -- Dave Peterson -- Dave Peterson |
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
reformat cells for date
David
51208 becomes May 12, 2008 when I run it through T to C and select MDY as format What are your short date settings? Gord Dibben MS Excel MVP On Wed, 2 Apr 2008 20:51:21 +0100, "David Biddulph" <groups [at] biddulph.org.uk wrote: I had wondered about that method, but for me that doesn't work with 51208, though it does work with 201208. -- David Biddulph |
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
reformat cells for date
02/04/2008
in other words DD/MM/YYYY -- David Biddulph "Gord Dibben" <gorddibbATshawDOTca wrote in message ... David 51208 becomes May 12, 2008 when I run it through T to C and select MDY as format What are your short date settings? Gord Dibben MS Excel MVP On Wed, 2 Apr 2008 20:51:21 +0100, "David Biddulph" <groups [at] biddulph.org.uk wrote: I had wondered about that method, but for me that doesn't work with 51208, though it does work with 201208. -- David Biddulph |
#13
Posted to microsoft.public.excel.misc
|
|||
|
|||
reformat cells for date
Mine are mm/dd/yyyy so that's the difference.
Gord On Wed, 2 Apr 2008 22:58:04 +0100, "David Biddulph" <groups [at] biddulph.org.uk wrote: 02/04/2008 in other words DD/MM/YYYY |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how to reformat numbers | Excel Discussion (Misc queries) | |||
Reformat numbers | Excel Discussion (Misc queries) | |||
Reformat spreadsheet | Excel Worksheet Functions | |||
Reformat Table | Excel Discussion (Misc queries) | |||
How to Stop Reformat When Cells Are Copied | Excel Discussion (Misc queries) |