| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Get the Date
I need to know, how to read out the dates from the Line
A1: Report 01.06.2005 - 30.06.2005 Thanks |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Get the Date
Put the following formula in B1
=MID(A1,FIND(" ",A1,1)+1,FIND(" ",A1,(FIND(" ",A1,1)+1))-FIND(" ",A1,1)) and the subsequent formula in C1 =MID(A1,FIND(B1,A1,1)+LEN(B1)+2,LEN(A1)-FIND(B1,A1,1)+LEN(B1)+2) It makes assumptions that cell A1 conistently follows the format you indicated below. -- http://HelpExcel.com 1-888-INGENIO 1-888-464-3646 x0197758 "Thomas" wrote: I need to know, how to read out the dates from the Line A1: Report 01.06.2005 - 30.06.2005 Thanks |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Get the Date
Thank you a lot, but i have to do this via VBA.
Thomas "galimi" wrote: Put the following formula in B1 =MID(A1,FIND(" ",A1,1)+1,FIND(" ",A1,(FIND(" ",A1,1)+1))-FIND(" ",A1,1)) and the subsequent formula in C1 =MID(A1,FIND(B1,A1,1)+LEN(B1)+2,LEN(A1)-FIND(B1,A1,1)+LEN(B1)+2) It makes assumptions that cell A1 conistently follows the format you indicated below. -- http://HelpExcel.com 1-888-INGENIO 1-888-464-3646 x0197758 "Thomas" wrote: I need to know, how to read out the dates from the Line A1: Report 01.06.2005 - 30.06.2005 Thanks |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Get the Date
Thomas,
Here is some code Dim iPos1 As Long Dim iPos2 As Long Dim iPos3 As Long Dim iPos4 As Long With Range("A1") iPos1 = InStr(1, .Value, " ") If iPos1 0 Then iPos2 = InStr(iPos1 + 1, .Value, " ") If iPos2 0 Then iPos3 = InStr(iPos2 + 1, .Value, " ") If iPos3 0 Then iPos4 = InStr(iPos3 + 1, .Value, " ") If iPos4 < 1 Then iPos4 = Len(.Value) + 1 End If MsgBox CDate(Replace(Mid(.Value, iPos1 + 1, iPos2 - 1 - iPos1), ".", "/")) MsgBox CDate(Replace(Mid(.Value, iPos3 + 1, iPos4 - 1 - iPos1), ".", "/")) End If End If End If End With -- HTH Bob Phillips "Thomas" wrote in message ... Thank you a lot, but i have to do this via VBA. Thomas "galimi" wrote: Put the following formula in B1 =MID(A1,FIND(" ",A1,1)+1,FIND(" ",A1,(FIND(" ",A1,1)+1))-FIND(" ",A1,1)) and the subsequent formula in C1 =MID(A1,FIND(B1,A1,1)+LEN(B1)+2,LEN(A1)-FIND(B1,A1,1)+LEN(B1)+2) It makes assumptions that cell A1 conistently follows the format you indicated below. -- http://HelpExcel.com 1-888-INGENIO 1-888-464-3646 x0197758 "Thomas" wrote: I need to know, how to read out the dates from the Line A1: Report 01.06.2005 - 30.06.2005 Thanks |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Concatenate including a date so that the date appears as a date | Excel Worksheet Functions | |||
| date in Cell to change colors if the date is beyond today's date | Excel Discussion (Misc queries) | |||
| Making a date go red, if date passes todays date. | Excel Worksheet Functions | |||
| Report Date - Date Recv = Days Late, but how to rid completed date | Excel Worksheet Functions | |||
| Date updates from worksheet to chart & changes date to a date series! Help!! | Charts and Charting in Excel | |||
Linear Mode
