cherry picking text to set variables

jkicker

Board Regular
Joined
Jun 7, 2018
Messages
79
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:
[TABLE="width: 500"]
<tbody>[TR]
[TD]

<colgroup><col width="104"></colgroup><tbody>
[TD="class: xl65, width: 104"]July 1, 2018 - June 30, 2019[/TD]

</tbody>
[/TD]
[/TR]
[TR]
[TD]

<colgroup><col width="104"></colgroup><tbody>
[TD="class: xl65, width: 104"]January 1, 2019 to December 31, 2019[/TD]

</tbody>
[/TD]
[/TR]
[TR]
[TD]

<colgroup><col width="104"></colgroup><tbody>
[TD="class: xl65, width: 104"]July 1, 2018 - June 30, 2019[/TD]

</tbody>
[/TD]
[/TR]
[TR]
[TD]

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

</tbody>
[/TD]
[/TR]
[TR]
[TD]

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

</tbody>
[/TD]
[/TR]
[TR]
[TD]

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

</tbody>
[/TD]
[/TR]
</tbody>[/TABLE]

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
 

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number
60 views and no response yet. is there a reason people aren't responding? usually someone responds.
 
Upvote 0
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)


Code:
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
ABCDE
11234
2Original TextFromToFromTo
3July 1, 2018 - June 30, 201971800
4January 1, 2019 to December 31, 2019132400
5July 1, 2018 - June 30, 201971800
6January 1 - June 30, 2019;January 1 - June 30, 202013182530
7July 1, 2018 to June 30, 201971800
8January 1 - June 30, 2018;July 1 - December 31, 2019161924
Sheet1
Cell Formulas
RangeFormula
B3=DateSplit($A3,B$1)
 
Upvote 0
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!
 
Upvote 0
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?
 
Upvote 0
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.
 
Upvote 0
Hey jkicker,

Try the revised below function…

Code:
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
 
Upvote 0

Forum statistics

Threads
1,223,958
Messages
6,175,633
Members
452,661
Latest member
Nonhle

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top