How to deal with a 14 month (period) year - VBA

[jlax2485]

Hi Everyone,

I am running into a frustrating problem with a VBA project I am working on. Our organization uses a 14 month year (12 regular months and 2 special posting periods for trailing charges), and I am not sure how to handle the two extra periods. I want to validate that the month of the column where I extracted the data matches the month of the column where the sum of this data will be placed.

For example, I thought of verifying that the MONTH( ) of each item in the ranges match, but it wont work with anything beyond 12. Our system uses the order of the month in the fiscal year, so (1/2018 = July 2017, 6/2018 = December 2017 and so forth). I could get around that with DATEADD( ) but it wont work with the two extra periods.

For periods 13 and 14, I want those values to be added onto June's total (the last month of our fiscal year). I have searched but cannot find anything useful to inspire me to get around this problem. Any help you can provide would be most appreciated!

 

[shg]

Not everything in those extra two months can be trailing charges (otherwise you'd just have 14-month 'years'). So those items (columns, entries, ???) must be annotated or segregated somehow, no?

 

[jlax2485]

Not everything in those extra two months can be trailing charges (otherwise you'd just have 14-month 'years'). So those items (columns, entries, ???) must be annotated or segregated somehow, no?

Unfortunately, Period 13 and 14 count for the fiscal year in which they are posted. They show up as columns of data from a system-generated report, with the "post period date" as the header.

I thought about using a select case statement to put each period into it's corresponding calendar date, and include 13 and 14 as "June 2017" but that would have to be updated each year.

 

[shg]

Dunno. Have no idea what your data looks like.

 

[jlax2485]

Dunno. Have no idea what your data looks like.

Account	GL	CATEGORY	001/2017	002/2017	003/2017	004/2017	005/2017	006/2017	007/2017	008/2017	009/2017	010/2017	011/2017	012/2017	013/2017	014/2017
80002575	615005	Personnel	5,000	(2,000)			50			10,000			25,000		(10,000)	25
80006933	991412	Other		15,000		(500)	2,500		35,000		25

<tbody>
</tbody>

Sorry about that - here is a rough idea of my source report. My destination template is similar, but I am using corresponding calendar year dates. I am putting the results of each column into an array, because there could be any number of accounts #'s that correspond to a particular line of business, and the sum of what is in each column of the array gets pasted into the destination template.

 

[shg]

OK -- so what's the question?

013/2017 are trailing charges for FY17

001/2018 is the same month with charges that apply to FY18.