Programming Logic

You know how programming logic can get gnarly? That happens when you need to make adjustments or additions to the program. Or you have a decision tree that is based upon time.

I’ currently trying to write a program to save “beginning of the month” data to use for comparison purposes throughout the month without blowing up the size of the file. Unfortunately, the data itself is blowing up the size.

You would think that this would be an easy exercise but it turned out to be a bit more complicated.

First, I had to make a decision of when to start holding the “beginning of the month” data. The first of the month sounds like a reasonable answer but the report is being created weekly, not daily, on a Sunday night or Monday morning. The first does not always fall on Sunday or Monday. The second complementary question was how far into the new month do we want to keep data? To clarify that problem statement, say Sunday falls on the fourth: do we want to keep a log of this data as the “beginning of the month” data to hold? I decided that for this report, the last week of the prior month was more likely to hold data most representative of the 1st of the new month than the days past the first. So basically, if a Sunday falls on dates between 24th through 1st, save the download data as the “beginning of the month” data for the succeeding month.

To capture data during the last week of the month, I began with the following logic: if today’s (Sunday’s) date + 7 days has a month greater than the month of today’s date, then save the today’s download as the starting point for the new month next week. At the time I was trying to solve this problem, I only knew about the auto open macro so the logic was altered a little to check the data from last week and start saving the data if the today’s month is greater than last week’s month.

But at the end of the year, when December turns into January, the month of January is less than the month number of December. This problem precipitated an addition of logic to check the year.

But then, I thought, “Wait a minute! I should just check the day, rather than the month and year.” At the end of every month, when the month flips over, the day decreases. So now the logic becomes, upon opening the file: if today’s (Sunday) date has a day (not date) that is less than the day of last week’s date, save last week’s download. Did you get that? To put in terms of an example, if today’s Sunday is the 3rd and last week it was the 26th, then save the download data of the 26th.

There was one little twitch to the logic: in some months, the 24th could fall on a Sunday and the following Sunday be the 1st. In this instance, we do not want to save data from the 24th, we actually want to save the 1st. So I added another little programming logic to test for this situation – basically if today’s date is the 1st, save the data and overwrite last week’s data download. Due to the timing of the instructions, the macro would save data from the 24th but when it encounters the date of the 1st, it was instructed to that data of the 1st.

All of this was developed by first thrashing out the timing on paper. On paper, I drew out what would happen if Sunday fell on a 28th, for example. I drew boxes of what should happen when. I did the same for the 22nd through the 3rd to flesh out any issues that might crop up, which was how I found the little twitch occurring around the 24th or 25th (depending on whether the month has 30 days or 31 days). Low tech to the rescue!

I still had to do a lot of testing and finetuning once I wrote up the program, but the main logic was developed on paper.