| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
how to convert a column of numbers (monthdayyear) into dates?
At issue is some numbers are 7 digits and some 8 (ie 7012006 or 10012006).
None of my attempts resulted in excel recognizing as valid dates. |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
how to convert a column of numbers (monthdayyear) into dates?
=IF(LEN(A1)=8,DATE(RIGHT(A1,4),LEFT(A1,2),MID(A1,3 ,2)),IF(LEN(A1)=7,DATE(RIGHT(A1,4),LEFT(A1,1),MID( A1,2,2))))
Vaya con Dios, Chuck, CABGx3 "wsucougar" wrote: At issue is some numbers are 7 digits and some 8 (ie 7012006 or 10012006). None of my attempts resulted in excel recognizing as valid dates. |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
how to convert a column of numbers (monthdayyear) into dates?
Hi,
try it =if(len(b2)=7,date(right(b2,4),left(b2,1),mid(b2,2 ,2)),date(right(b2,4),left(b2,2),mid(b2,3,2))) hth regards from Brazil Marcelo "wsucougar" escreveu: At issue is some numbers are 7 digits and some 8 (ie 7012006 or 10012006). None of my attempts resulted in excel recognizing as valid dates. |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
how to convert a column of numbers (monthdayyear) into dates?
On Tue, 15 Aug 2006 10:37:02 -0700, wsucougar
wrote: At issue is some numbers are 7 digits and some 8 (ie 7012006 or 10012006). None of my attempts resulted in excel recognizing as valid dates. Here's one method: =DATE(MOD(A1,10^4),INT(A1/10^6),MOD(INT(A1/10^4),100)) --ron |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
how to convert a column of numbers (monthdayyear) into dates?
Sorry, forgot the final case of the cell being anything other than containing
7 or 8 characters........... =IF(LEN(A1)=8,DATE(RIGHT(A1,4),LEFT(A1,2),MID(A1,3 ,2)),IF(LEN(A1)=7,DATE(RIGHT(A1,4),LEFT(A1,1),MID( A1,2,2)),"")) Vaya con Dios, Chuck, CABGx3 "CLR" wrote: =IF(LEN(A1)=8,DATE(RIGHT(A1,4),LEFT(A1,2),MID(A1,3 ,2)),IF(LEN(A1)=7,DATE(RIGHT(A1,4),LEFT(A1,1),MID( A1,2,2)))) Vaya con Dios, Chuck, CABGx3 "wsucougar" wrote: At issue is some numbers are 7 digits and some 8 (ie 7012006 or 10012006). None of my attempts resulted in excel recognizing as valid dates. |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
how to convert a column of numbers (monthdayyear) into dates?
Thanks for your help it was just what I needed and it works great!
"CLR" wrote: Sorry, forgot the final case of the cell being anything other than containing 7 or 8 characters........... =IF(LEN(A1)=8,DATE(RIGHT(A1,4),LEFT(A1,2),MID(A1,3 ,2)),IF(LEN(A1)=7,DATE(RIGHT(A1,4),LEFT(A1,1),MID( A1,2,2)),"")) Vaya con Dios, Chuck, CABGx3 "CLR" wrote: =IF(LEN(A1)=8,DATE(RIGHT(A1,4),LEFT(A1,2),MID(A1,3 ,2)),IF(LEN(A1)=7,DATE(RIGHT(A1,4),LEFT(A1,1),MID( A1,2,2)))) Vaya con Dios, Chuck, CABGx3 "wsucougar" wrote: At issue is some numbers are 7 digits and some 8 (ie 7012006 or 10012006). None of my attempts resulted in excel recognizing as valid dates. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Sum/average numbers in column A dependant on value in column B | Excel Worksheet Functions | |||
| Conditional Format as a MACRO | Excel Worksheet Functions | |||
| Need to Improve Code Copying/Pasting Between Workbooks | Excel Discussion (Misc queries) | |||
| Consecutive Numbers down a column not to Exceed 49 | Excel Worksheet Functions | |||
| How do you convert numbers as "text" to values for a long column . | Excel Discussion (Misc queries) | |||
Linear Mode
