| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
convert 1006 to date (01/10/2006)
I have the following requirement. In a cell i have dates that have been
imported from another system. They are entered as follows, 1206 would be December 2006 605 would be June 2005 0101 would be January 2001 There are no date older than 0101. I would like to convert the cells or copy to new cells in the following format 01/12/2006. Because there is no specific day of the month in the original data i am happy to have 01 as the default. is this easily achievable? |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
convert 1006 to date (01/10/2006)
=DATE(2000+RIGHT(A1,2)*1,LEFT(A1,LEN(A1)-2),1)
-- Gary''s Student gsnu200710 "Maax" wrote: I have the following requirement. In a cell i have dates that have been imported from another system. They are entered as follows, 1206 would be December 2006 605 would be June 2005 0101 would be January 2001 There are no date older than 0101. I would like to convert the cells or copy to new cells in the following format 01/12/2006. Because there is no specific day of the month in the original data i am happy to have 01 as the default. is this easily achievable? |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
convert 1006 to date (01/10/2006)
Just some added information:
Gary's response provides an actual date serial number which can be used in further calculations. To have it appear as you specify, you would need to format the cell with the formula with the appropriate date format you want to see. -- Regards, Tom Ogilvy "Gary''s Student" wrote: =DATE(2000+RIGHT(A1,2)*1,LEFT(A1,LEN(A1)-2),1) -- Gary''s Student gsnu200710 "Maax" wrote: I have the following requirement. In a cell i have dates that have been imported from another system. They are entered as follows, 1206 would be December 2006 605 would be June 2005 0101 would be January 2001 There are no date older than 0101. I would like to convert the cells or copy to new cells in the following format 01/12/2006. Because there is no specific day of the month in the original data i am happy to have 01 as the default. is this easily achievable? |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
convert 1006 to date (01/10/2006)
Hi Gary, thanks very much, exactly what i needed.
"Gary''s Student" wrote: =DATE(2000+RIGHT(A1,2)*1,LEFT(A1,LEN(A1)-2),1) -- Gary''s Student gsnu200710 "Maax" wrote: I have the following requirement. In a cell i have dates that have been imported from another system. They are entered as follows, 1206 would be December 2006 605 would be June 2005 0101 would be January 2001 There are no date older than 0101. I would like to convert the cells or copy to new cells in the following format 01/12/2006. Because there is no specific day of the month in the original data i am happy to have 01 as the default. is this easily achievable? |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
convert 1006 to date (01/10/2006)
try this. Notice the ONE dot before the items in the with statement.
Sub dodatefromtext() For Each c In Selection With c .Value = DateSerial(Right(c, 2), Left(c, Len(c) - 2), 1) .NumberFormat = "dd/mm/yyyy" End With Next c End Sub -- Don Guillett SalesAid Software "Maax" wrote in message ... I have the following requirement. In a cell i have dates that have been imported from another system. They are entered as follows, 1206 would be December 2006 605 would be June 2005 0101 would be January 2001 There are no date older than 0101. I would like to convert the cells or copy to new cells in the following format 01/12/2006. Because there is no specific day of the month in the original data i am happy to have 01 as the default. is this easily achievable? |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
convert 1006 to date (01/10/2006)
Orginal posting of the question answered.
-- Regards, Tom Ogilvy "Maax" wrote: I have the following requirement. In a cell i have dates that have been imported from another system. They are entered as follows, 1206 would be December 2006 605 would be June 2005 0101 would be January 2001 There are no date older than 0101. I would like to convert the cells or copy to new cells in the following format 01/12/2006. Because there is no specific day of the month in the original data i am happy to have 01 as the default. is this easily achievable? |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How to convert date to financial year format 2006-07 | Excel Worksheet Functions | |||
| What's difference in writing date as 18/10/2006 and '18/10/2006 | Excel Discussion (Misc queries) | |||
| Convert date format from 20060801 to 1st Aug 2006 (or similar)? | Excel Discussion (Misc queries) | |||
| turn off convert text to date (i.e. 4-9 to April 9, 2006) | Excel Worksheet Functions | |||
| How do I change a date format 3/14/2006 to 3-14-2006 in Excel 2002 | Excel Discussion (Misc queries) | |||
Hybrid Mode
