Pulling date from string

[vzq032372]

Hello All,

I'm not very familiar with creating my own functions but found this online and am trying to manipulate it to my own need. I recorded the formulas and then replaced the range address with "strInputDate" but I'm still getting an "invalid procedure call or argument". it stops at the first formula for month. Any clues?

Public Function getDate(strInputDate As String) As Date
    'Variable Declaration
    Dim iMonth As Integer
    Dim iDay As Integer
    Dim lYear As Long
    '
    'Get month
        iMonth = Mid(strInputDate, InStr(":", strInputDate) + 1, InStr("/", strInputDate) - 1 - InStr(":", strInputDate))
'
    'Get day
        iDay = Mid(strInputDate, InStr("/", strInputDate) + 1, InStr("/", strInputDate, InStr("/", strInputDate) + 1) - InStr("/", strInputDate) - 1)
        
    'Get year
     If Len(Mid(strInputDate, InStr("/", strInputDate, InStr("/", strInputDate) + 1) + 1, 4)) = 2 Then
        lYear = "20" & Mid(strInputDate, InStr("/", strInputDate, InStr("/", strInputDate) + 1) + 1, 4)
        
        Else: iYear = Mid(strInputDate, InStr("/", strInputDate, InStr("/", strInputDate) + 1) + 1, 4)
    End If
    'Create a complete date and return the value
    getDate = CDate(MonthName(iMonth) & "/" & iDay & "/" & lYear)
    '
End Function

 

[Joe4]

I think it would be better if you show us a bunch of examples of what your strings look like, and what you are trying to pull from them.

 

[vzq032372]

Attached is a sample image, I couldn't figure out how to post the file. Basically I need to extract the dates from Q8 and Q10. These dates will not always be in these fields and they will be in different formats mm/d/yyyy, mm/d/yy or m/d/yy or mm/dd/yyyy (this is the format I ultimately want)

 

[vzq032372]

Hello All,

I'm not very familiar with creating my own functions but found this online and am trying to manipulate it to my own need. I recorded the formulas and then replaced the range address with "strInputDate" but I'm still getting an "invalid procedure call or argument". it stops at the first formula for month. Any clues?

Public Function getDate(strInputDate As String) As Date
    'Variable Declaration
    Dim iMonth As Integer
    Dim iDay As Integer
    Dim lYear As Long
    '
    'Get month
        iMonth = Mid(strInputDate, InStr(":", strInputDate) + 1, InStr("/", strInputDate) - 1 - InStr(":", strInputDate))
'
    'Get day
        iDay = Mid(strInputDate, InStr("/", strInputDate) + 1, InStr("/", strInputDate, InStr("/", strInputDate) + 1) - InStr("/", strInputDate) - 1)
      
    'Get year
     If Len(Mid(strInputDate, InStr("/", strInputDate, InStr("/", strInputDate) + 1) + 1, 4)) = 2 Then
        lYear = "20" & Mid(strInputDate, InStr("/", strInputDate, InStr("/", strInputDate) + 1) + 1, 4)
      
        Else: iYear = Mid(strInputDate, InStr("/", strInputDate, InStr("/", strInputDate) + 1) + 1, 4)
    End If
    'Create a complete date and return the value
    getDate = CDate(MonthName(iMonth) & "/" & iDay & "/" & lYear)
    '
End Function

I think it would be better if you show us a bunch of examples of what your strings look like, and what you are trying to pull from them.

Samplebook.xlsx
	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z
1
2
3		E S T I M A T E S U M M A R Y
4
5
6		Project Title: Line 125														Estimate By:
7
8		Project Mgr/Lead:										2020				Date of Estimate: 02-28-20
9												12
10		Project Number:20169										15				ISD: 12/15/20
11		WO&Task#: 02368367
12		Est. Revision # 00														Estimate # E-20-51
13																								Template Revision # 01.3
14
15		ESTIMATE SUMMARY
16
17		Estimate Type: Planning
18
19	WBS			TOTAL	% of Total	Prior			2020			2021			2022			2023			2024			2025
20	1	Construction		$10,397,200	54.4%				$10,397,200
21	2	Engineering / Design		$633,500	3.3%	$6,500			$627,000
22	3	Land
23	4	Material		$2,126,700	11.1%				$2,126,700
24	5	Project Mgmt. & Sppt.		$925,400	4.8%	$1,400			$924,000
25	6	Removal		$423,400	2.2%				$423,400
26	7	Test
27	8	Risk / Contingency		$975,000	5.1%				$975,000
28	9	Escalation
29	10	Indirects		$3,015,300	15.8%	$3,200			$3,012,100
30	11	AFUDC		$603,600	3.2%				$603,600
31		Total Cost		$19,100,100	100.0%	$11,100			$19,089,000
32
33
34		Estimate Range		-25%	-	25%
35				$14,325,000	-	$23,875,000
36
37		NOTE: Risk / Contingency is 5.4% of Project Costs (incl escal/indirects/AFUDC)
38
39		COMMENTS:
40
41		Project Scope:
42
43
44
45
46
47
48
Cover

Cell Formulas
Range	Formula
M8	M8	=IF(LEN(MID(Q10, FIND("/",Q10, FIND("/",Q10)+1)+1,4))=2,"20" & MID(Q10, FIND("/",Q10, FIND("/",Q10)+1)+1,4),MID(Q10, FIND("/",Q10, FIND("/",Q10)+1)+1,4))
M9	M9	=MID(Q10,FIND(":",Q10)+1,FIND("/",Q10)-1-FIND(":",Q10))
M10	M10	=MID(Q10,FIND("/",Q10)+1,FIND("/",Q10,FIND("/",Q10)+1)-FIND("/",Q10)-1)

 

[Georgiboy]

Could tru a amll UDF:

Function FixDate(str As String) As Date
    Dim re As Object
    
    Set re = CreateObject("vbscript.regexp")
    With re
        .Pattern = "[^\d+/-]"
        .Global = True
        FixDate = CDate(.Replace(str, ""))
    End With
End Function

Used like:

Book1
	B	C
2	Date of Estimate: 02-28-20	28/02/2020
3	ISD@ 12/13/20	13/12/2020
4	Cheese 4/18/2022	18/04/2022
5	Sandwich 03/21/22	21/03/2022
Sheet1

Cell Formulas
Range	Formula
C2:C5	C2	=FixDate(B2)

 

[vzq032372]

Could tru a amll UDF:

Function FixDate(str As String) As Date
    Dim re As Object
   
    Set re = CreateObject("vbscript.regexp")
    With re
        .Pattern = "[^\d+/-]"
        .Global = True
        FixDate = CDate(.Replace(str, ""))
    End With
End Function

Used like:

Book1
	B	C
2	Date of Estimate: 02-28-20	28/02/2020
3	ISD@ 12/13/20	13/12/2020
4	Cheese 4/18/2022	18/04/2022
5	Sandwich 03/21/22	21/03/2022
Sheet1

Cell Formulas
Range	Formula
C2:C5	C2	=FixDate(B2)

Although there is a date it's returning the following (see image). The dates will always have the month first, that might be the error.

 

[vzq032372]

 

[vzq032372]

View attachment 73739

I got it to work after changing the formula in the vba to
Range("I1").Select
ActiveCell = "=FixDate(" & ISD.Address & ")"