IsDate and CDate Help

[dellehurley]

Hi
I import information into an excel database from a csv file, the date column contains various formats like this...

Data_Entry_Form_ver_25a.xlsm
	A	B	C
1	RIN	Surname, Given names	BirthDt
2	2644	Dobell, Glenn	3 Apr 1893
3	2645	Weston, Vivienne Doris	1896
4	3996	Thornton,
5	3997	Haydon, Hilton Bloomfield	21-Jan-09
6	5607	Humble, Alice Gloria	21-May-11
7	5608	Crowley, Annie
8	5626	McIntyre, John Edwin	1862
9	5627	Young, Florence Caroline	1862
10	5777	Capel, Clive Ross	13 Mar 1894
11	6981	Hurley, Daniel Patrick	10 Mar 1875
12	6982	Hurley, Jerome John
13	7164	Hammond, Neville	Abt. 1940
Sheet1

I am trying to create a loop and use the isdate and cdate function. This way at least all the full dates are in date format but I cannot get it right. Please help.
I only need to extract the year however I don't think I can do this in one step so I am only dealing with the fulldates at this stage.

This is my code

Dim lRow_Dest As Long
Dim DateFormat As String
Dim i As Long
Dim DateSearch As Long

lRow_Dest = wsDest.Cells(wsDest.Rows.Count, "A").End(xlUp).Offset(0).Row
DateFormat = IsDate.Value
DateSearch = Application.WorksheetFunction.CountIf(shImport.Range("C:C"), DateFormat)

    If DateSearch >= True Then
        For i = 2 To lDestLastRow
            If shImport.Cells(i, 3) = DateFormat Then
            Cells(i, 3).Value = CDate(Range(i, 3).Value)
            End If
        Next i
    End If

The error is argument not optional

 

[Peter_SSs]

Assuming column C is all text values, could you use a worksheet formula instead of vba?

BTW, I suggest that you update your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

21 09 18.xlsm
	C	D
1	BirthDt	Year
2	3 Apr 1893	1893
3	1896	1896
4
5	21-Jan-09	2009
6	21-May-11	2011
7
8	1862	1862
9	1862	1862
10	13 Mar 1894	1894
11	10 Mar 1875	1875
12
13	Abt. 1940	1940
Year

Cell Formulas
Range	Formula
D2:D13	D2	=IF(C2="","",IFERROR(RIGHT(C2,4)+0,YEAR(DATEVALUE(C2))))

 

[dellehurley]

Assuming column C is all text values, could you use a worksheet formula instead of vba?

BTW, I suggest that you update your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

21 09 18.xlsm
	C	D
1	BirthDt	Year
2	3 Apr 1893	1893
3	1896	1896
4
5	21-Jan-09	2009
6	21-May-11	2011
7
8	1862	1862
9	1862	1862
10	13 Mar 1894	1894
11	10 Mar 1875	1875
12
13	Abt. 1940	1940
Year

Cell Formulas
Range	Formula
D2:D13	D2	=IF(C2="","",IFERROR(RIGHT(C2,4)+0,YEAR(DATEVALUE(C2))))

Hi Peter,
Thanks for your reply. This works perfectly on the data except for the dates eg. the date in row 5 is "21 Jan 1909" not "21 Jan 2009" and row 6 is "21 May 1911".

Also thanks for bringing my attention to me account details they are now updated. FYI Windows 10 & Office 365.
Dannielle

 

[dellehurley]

Hi Peter,
Thanks for your reply. This works perfectly on the data except for the dates eg. the date in row 5 is "21 Jan 1909" not "21 Jan 2009" and row 6 is "21 May 1911".

Also thanks for bringing my attention to me account details they are now updated. FYI Windows 10 & Office 365.
Dannielle

I have
=IF(CELL("format",C2)="D1",(YEAR(C2)),(RIGHT(C2,4)))
this gives me the correct year for all formats. Thanks for your help I needed the nudge in the right direction.
Dannielle

 

[Peter_SSs]

the date in row 5 is "21 Jan 1909" not "21 Jan 2009"

There wasn't anything to indicate that.

Anyway, glad you got it resolved.

 

[Alex Blakenburg]

I have
=IF(CELL("format",C2)="D1",(YEAR(C2)),(RIGHT(C2,4)))
this gives me the correct year for all formats. Thanks for your help I needed the nudge in the right direction.
Dannielle

Are you sure ?
As far as I can tell it gives the same results as Peter's formula, except that it leaves some years a text and others as numbers.

If you know for sure that all the years are going to be < 2000, your formula needs minus 100 added to the Year version

=IFERROR(IF(CELL("format",C2)="D1",YEAR(C2)-100,RIGHT(C2,4)+0),"")