Always test your program

I’ve been testing, testing, and testing the ACA reporting, trying to make sure the results are right. I had about 50% of the reports done with the hope that an assumption for the additional 40% would turn out to be right.

For me, dates are always problematic. There are always unforeseen results that come out of my logic statements or formulas, which is why I test so much. In this reporting exercise, there is just so much going on between the hire date, the term date, and the coverage dates. There are also what I call the edge dates: if you are let go on 1/31/2016, could you be considered as having worked the entire month, ie, you worked on 1/31/2016? If you were let go on 1/1/2016, then could you be considered as not having worked at all in 2016? What if you said the person worked on 1/31 but did not work on 1/1?

These transition dates are nasty.

So my testing consisted of filtering for different conditions and checking to see if the results worked as planned. For example, if I filter for dates between hired before 1/1/2015 and let go after 12/31/2015 (basically employed for the entire year of 2015) and had coverage starting on 1/1/2015 or earlier, then everybody in the resultant filter should show a coverage indicator (1B, 1C, 1D, or 1E) for every month. I have a testing formula for each row (representative of each person) with a COUNTIF formula to count how many months had the same coverage indicator as the one in January. If the count is 12, then the person had that coverage indicator for the entire year. To repeat again and clarify: if January had a coverage indicator of 1B and the COUNTIF formula comes up 12, then that person received coverage the entire year.

That is just one example of a test. There is a test to see what happens if a person is hired in the middle of the year, what happens if he is let go sometime in the year, and so on.

My tests were coming up all stars so I had hopes of beginning the printing process.

Until I met with our HR VP. Okay, there was one situation I hadn’t considered and that was people rolling off coverage due to changing situations (not due to termination). I had no idea that people could opt out mid-year; I thought you couldn’t change coverage outside of the once-a-year re-enrollment date. Okay, we had to go back to the healthcare provider and ask for more information, basically the term dates. I got a new run on Friday and now I have folks who showing up multiple times, up to 5 times, with differing dates. I’m trying to figure out how to pull the correct dates.

So I’m starting over and I’m getting kind of nervous.