cherry picking text to set variables

[jkicker]

basically our fiscal year runs from May to April and i need to count the number of months in a range that falls inside those dates. Here's a snippet of what my data looks like:

Period of leave:

July 1, 2018 - June 30, 2019 <colgroup><col width="104"></colgroup><tbody> </tbody>

January 1, 2019 to December 31, 2019 <colgroup><col width="104"></colgroup><tbody> </tbody>

July 1, 2018 - June 30, 2019 <colgroup><col width="104"></colgroup><tbody> </tbody>

Split leave: January 1 - June 30, 2019; January 1 - June 30, 2020 <colgroup><col width="104"></colgroup><tbody> </tbody>

July 1, 2018 to June 30, 2019 <colgroup><col width="104"></colgroup><tbody> </tbody>

Split leave: January 1 - June 30, 2018; July 1 - December 31, 2019 <colgroup><col width="104"></colgroup><tbody> </tbody>

<tbody>
</tbody>

so as you can see its not coded very consistently, but what is consistent is that the months are all written out in full and the years are all in YYYY format. There is a range of 48 months (Jan '18-Dec '21). i need to scan the text in each cell and assign 2 variables, one for the start month and one for the end month (and corresponding year, so a number from 1-48), unless it is a split leave, in which case we need 4 variables. we can assume that if there isn't a year next to the first month, that it is the same as the year for the second month. so for example, if the leave says "February 1 - June 30, 2019", then my variables will have to be m1 = 14 and m2 = 18.
Please help this n00b

 

[jkicker]

60 views and no response yet. is there a reason people aren't responding? usually someone responds.

 

[mse330]

This is a UDF (User Defind Function) that should do what you need based on the above sample provided. The function takes 2 arguments, the first being the text that you need to extract the dates from & the 2nd argument is which date you are looking for (you should specify by 1,2,3 or 4) referencing m1, m2, m3 & m4 (3 & 4 in case of a split leave)

Function DateSplit(Txt As String, Period As Byte) As String
Txt = Replace(Txt, " to ", " - ")
Txt = Replace(Txt, ";", " - ")
On Error GoTo Err
If Not Split(Txt, " - ", , 1)(Period - 1) Like "*20**" Then
    DateSplit = DateSerial(Year(Split(Txt, " - ", , 1)(Period)), _
    Month(Split(Txt, " - ", , 1)(Period - 1)), Day(Split(Txt, " - ", , 1)(Period - 1)))
Else
    DateSplit = Split(Txt, " - ", , 1)(Period - 1)
End If
Select Case Year(DateSplit) - 2018
    Case 0: DateSplit = Month(DateSplit) + 0
    Case 1: DateSplit = Month(DateSplit) + 12
    Case 2: DateSplit = Month(DateSplit) + 24
    Case 3: DateSplit = Month(DateSplit) + 36
End Select
Exit Function
Err:
DateSplit = 0
End Function

Book1
	A	B	C	D	E
1		1	2	3	4
2	Original Text	From	To	From	To
3	July 1, 2018 - June 30, 2019	7	18	0	0
4	January 1, 2019 to December 31, 2019	13	24	0	0
5	July 1, 2018 - June 30, 2019	7	18	0	0
6	January 1 - June 30, 2019;January 1 - June 30, 2020	13	18	25	30
7	July 1, 2018 to June 30, 2019	7	18	0	0
8	January 1 - June 30, 2018;July 1 - December 31, 2019	1	6	19	24
Sheet1

Cell Formulas
Range	Formula
B3	=DateSplit($A3,B$1)

 

[jkicker]

this is excellent, nearly perfect. i had to code my data a little bit cuz sometimes there was no space around the "-" but it was easy and it's done and THANK YOU!!!! SO MUCH!

 

[mse330]

Glad to help & thanks for reporting back

 

[jkicker]

so i'm having a problem with a later stage in my project where i need to compare the result of this formula to an integer, and i'm getting bad results because this function returns a string. i need the function to return an integer, but i'm worried if i change the first line of the function that it will affect the other parts of the function, ie: the if not section. any suggestions?

 

[jkicker]

so i've used a workaround that just has me putting this function inside a value function ie =value(datesplit(txt, period)), but i don't need this as a string at all. so if any of you have suggestions to make the formula return an integer, that would be great.

 

[mse330]

Hey jkicker,

Try the revised below function…

Function DateSplit(Txt As String, Period As Byte) As Variant
Txt = Replace(Txt, "to", " - ")
Txt = Replace(Txt, ";", " - ")
On Error GoTo Err
If Not Split(Txt, " - ", , 1)(Period - 1) Like "*20**" Then
    DateSplit = DateSerial(Year(Split(Txt, " - ", , 1)(Period)), _
    Month(Split(Txt, " - ", , 1)(Period - 1)), Day(Split(Txt, " - ", , 1)(Period - 1)))
Else
    DateSplit = Split(Txt, " - ", , 1)(Period - 1)
End If
DateSplit = (Year(DateSplit) Mod 2018) * 12 + Month(DateSplit)
Exit Function
Err:
DateSplit = 0
End Function

 

[jkicker]

Another perfect solution. Many many thanks

 

[mse330]

Glad to help ... You’re most welcome