Date Formula Confusion
At one time, my understanding of the date formulas we use in Recurring Journals was about as clear as mud. But recently I had an amazing “Aha” moment. I came up with a way to remember something that was about to make entering date formulas easier.
The “Old” Way
When learning how to use Recurring General Journals for month end, I was taught how to set the date formula for the Recurring Frequency so that on posting the journal, the date would be set for the next month’s posting. This meant that the Recurring Frequency date formula needed to instruct Business Central to set the date on the journal, after posting, to the last date of the next month. My recurring journal would be all set for next month end.
1d+1m-1d
I am one of those “Why?” people. I need to know the “why” behind something so that I can remember it. But the “why” behind the formula I needed to remember to make the last day of next month be the new date in my recurring journals wasn’t so easy to remember. Technically, once you set the formula up in the field, you don’t need to remember it. It is there, it does its thing. But you see, I’m a trainer and I need to explain how to do things in Business Central. How can I explain something I can’t remember?
The formula I was using was “1d+1m-1d.” To understand how it works, I was told that “1d” will add one day to the current date (the posting date on the journal now,) and “+1m” will add one month to that date, and “-1d” will take away one day from that date leaving you with the last day of next month.
Example: When February 29th is the current date on the journal lines, on posting, the new date will be calculated as follows:
- Add one day to get March 1.
- To March 1, add one month to get to the first day of the next month, or April 1.
- To April 1 subtract 1 day to get to the last day of the prior month or March 31, and the last day of our next month. March 31 will be the new posting date on the recurring general journal lines.
This will work every time no matter the date you start with in your journal. But Yikes! This is not only confusing but hard to remember, right?
“C” Means…
First of all, we are going to use a “C” in our new date formula. “C” according to the Microsoft documentation is translated to “Current.”
Using the above meanings for the formula highlighted in the next illustration, we can translate “C” to mean “Current,” “M” to mean “Month,” therefore “CM” means “Current Month.” And further translated in the table from Microsoft, “Current Month” can also be interpreted to mean “last day of the month.”
The Righter Way to think of “C” in date formulas
Let’s think of “C” a little differently. What if we translate “C” to mean “Completion of the”? Now when we translate the above formula we can say “C” means “Completion of the,” and “M” means “Month,” so “CM” means “Completion of the Month” or “last day of the month”
Following this translation, “CW” would be “Completion of the Week.” Or last day of the week. And it does work this way. (BTW, Sunday is the last day of the week in my system when using this formula.)
Using the end of month formula in Recurring General Journals
I need to thank Robb Delprado for sharing a more concise formula to get to the last day of the next month. The formula Robb shared with me is “1M+CM.”
If we incorporate my translation of C, we can translate this formula as “1M (add 1 month) +(and) CM (get the Completion of the Month)”
Example: When February 29th is the current date on the journal lines, on posting, the new date will be calculated as follows:
- Add one month to get March 29
- Go to completion of the month of March. March 31 is the last day of the month. March 31 will be the new posting date on the recurring general journal lines.
This will work every time no matter the date you start with in your journal. And OK, so it is still a bit confusing, but I think it is easier to understand and thereby remember.
You Decide
As is very common in Business Central, you have a choice. “1M+CM” or “1d+1M-1d”, you decide what formula is The Righter Way for you.
Let us know in the comments what date formulas you use in Business Central and what you do to help remember them.
