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
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>
[/TR]
[TR]
[TD]
<colgroup><col width="104"></colgroup><tbody>
[TD="class: xl65, width: 104"]January 1, 2019 to December 31, 2019[/TD]
</tbody>
[/TR]
[TR]
[TD]
<colgroup><col width="104"></colgroup><tbody>
[TD="class: xl65, width: 104"]July 1, 2018 - June 30, 2019[/TD]
</tbody>
[/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>
[/TR]
[TR]
[TD]
<colgroup><col width="104"></colgroup><tbody>
[TD="width: 104"] July 1, 2018 to June 30, 2019[/TD]
</tbody>
[/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>
[/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