VBA Code to extract date range from Cell?

[Coyotex3]

Hi, trying to figure out a code which will allow me to extract a date range from a cell. I was able to do it using Formulas(TextBefore and TextAfter) but would like to be able to insert this inside of a larger code.

Ideally, I'd like to go from this:

Book2
	A	B	C	D
1	Account Type	Notes	Total	Date Range
2	Bills	1357888 (1/01/2020-2/01/2020)	1000
3	Bills	Monthly (1/01/2020-2/01/2020)	500
4	Bills	Expenses (02/01/2021-3/01/2021)	1000
5	Bills	6000 (1/01/2020-2/01/2020)	500
Sheet1

To this:

Book2
	A	B	C	D
1	Account Type	Notes	Total	Date Range
2	Bills	1357888 (1/01/2020-2/01/2020)	1000	1/01/2020-2/01/2020
3	Bills	Monthly (1/01/2020-2/01/2020)	500	1/01/2020-2/01/2020
4	Bills	Expenses (02/01/2021-3/01/2021)	1000	02/01/2021-3/01/2021
5	Bills	6000 (1/01/2020-2/01/2020)	500	1/01/2020-2/01/2020
Sheet1

Note: Range D3 contains a different date format than the other cells in Column D. The date ranges will sometimes be written as 01/01/2020 or 1/1/2020 or 01/1/2020 or 1/01/2020 etc. Is there a way of making excel recognize the different date formats and extracting them?

 

[Coyotex3]

You can probably add standardising the date format to @James006's evaluate function but here is another option.
Note: It assumes that the date is in the format of your region setting. If you need to reverse day & month it will need to be modified.

Sub SplitOutAndFormatDate()

    Dim wb As Workbook
    Dim sht As Worksheet
    Dim Rng As Range, destRng As Range
    Dim arr As Variant
    Dim dtFrom As Date, dtTo As Date
    Dim DateFormat As String
    Dim strSplit As Variant
    Dim LastRow As Long, i As Long
   
    Set wb = ActiveWorkbook
    Set sht = wb.Worksheets("Sheet1")       '<--- Change this to the name of your sheet or to ActiveSheet
    DateFormat = "dd/mm/yyyy"               '<--- Change to desired format
   
    LastRow = sht.Range("B" & Rows.Count).End(xlUp).Row
    With sht
        Set Rng = .Range("B2:D" & LastRow)
        arr = Rng.Value
    End With

    For i = 1 To UBound(arr)
        strSplit = Split(arr(i, 1), "(")
        strSplit = Split(Left(strSplit(1), Len(strSplit(1)) - 1), "-")
        dtFrom = DateValue(strSplit(1))
        dtTo = DateValue(strSplit(1))
        arr(i, 3) = Format(dtFrom, DateFormat) & "-" & Format(dtTo, DateFormat)
    Next i
   
    Rng.Columns(3) = Application.Index(arr, 0, 3)

End Sub

Alex my apologies, on closer inspection, it seems like I'm not getting the desired output with your code. This is what I get:

Book2
	A	B	C	D
1	Account Type	Notes	Total	Date Range
2	Bills	1357888 (1/01/2020-2/01/2020)	1000	01/02/2020-01/02/2020
3	Bills	Monthly (1/01/2020-2/01/2020)	500	01/02/2020-01/02/2020
4	Bills	Expenses (02/01/2021-3/01/2021)	1000	01/03/2021-01/03/2021
5	Bills	6000 (1/01/2020-2/01/2020)	500	01/02/2020-01/02/2020
Sheet1

The output seems to be off.

 

[Alex Blakenburg]

Thanks for testing both.
I mainly posted to address the part about making the output date a consistent.

For the from date error change this line to use 0 instead of 1
dtFrom = DateValue(strSplit(0))

It looks like you are using US date format so change this line to mm/dd or m/dd (currently dd/mm) if you don't want the leading 0
DateFormat = "mm/dd/yyyy"

 

[Coyotex3]

Thanks for testing both.
I mainly posted to address the part about making the output date a consistent.

For the from date error change this line to use 0 instead of 1
dtFrom = DateValue(strSplit(0))

It looks like you are using US date format so change this line to mm/dd or m/dd (currently dd/mm) if you don't want the leading 0
DateFormat = "mm/dd/yyyy"

Alex, thank you once again! I overlooked the date format at the top along with the instructions to change it to the desired format! It works beautifully now

 

[Alex Blakenburg]

Alex, thank you once again! I overlooked the date format at the top along with the instructions to change it to the desired format! It works beautifully now

To be fair I still had an error in the dtFrom split . Appreciate you having tested it and providing feedback.