How to best use date calculations in Business Central

In the attached video (access on YouTube) you will learn about how dates work in Business Central. What are the shortcuts to enter dates and the differences between work date and system date? Find out how date filters can be built using date formulas and date calculations. Then put all this knowledge to use by using saved views to reuse filters that you’ve built.

In the sections below we’ll give some high level points for each area.

Business Central Date Shortcuts

There are a number of ways to enter dates quickly. The main one is to exclude the forward slashes from the date. For instance, entering 150524 is the same as entering 15/05/2024.

You can also enter part dates. For example, if you enter the day of the month it will add the month and year. If the system date is 15/05/2024 and you enter 31 in a date field it will complete as 31/05/2024.

What is the system date? The system date is the actual date based on your user time zone in “My Settings”. You can quickly enter the system date by entering the letter T in a date field. It is not case sensitive.

What is the work date? By default the work date is set to the system date every time you login. However, the user can change the work date to a different date in “My Settings”. The work date is used when automatically populating key date fields in Business Central IE Posting Date or Document Date. You can quickly enter the work date by entering the letter W in a date field. It is not case sensitive.

Simple Date Filters

Whilst you can use a number of different date filters in Business Central the simplest one is two full stops.

..

You use the two full stops to set a range. The easiest way to think of them are to/from. If the dots go before a date then they are to (and including) that date. This means ..18/05/2024 will include everything up to and on 18/05/2024. If the dots go after a date then they are from (and including that date). Therefore, 18/05/2024.. will show everything on 18/05/2024 and after that date. If the dots go between two dates then they are from one date to the other (inclusive). So, 01/05/2024..18/05/2024 will include everything between and on those dates.

Date Formulas and Date Calculations

Date formulas are used to carry out date calculations in Business Central. A series of numbers and letters can be used to manipulate one date into another. The letters are:

• • D = days

• • W = weeks

• • M = months

• • Q = quarters

• • Y = years

• • C = current (more about this in a bit)

The letters are not case sensitive.

Normally you would use one of the date shortcuts such as W or T as a starting point and work from there. As the work date is set to todays date each time you login (unless you change it in a session) then I prefer to start with W. But different use cases might mean you want to use a T rather than a W.

The simplest date calculation would be something like W+5D this means use the work date and add 5 days to it. If the work date is 18/05/2024 then adding 5 days to it would give you 23/05/2024. W-5D would take you back 5 days to 13/05/2024.

Depending on how C (current) is used it will take you to the beginning (-) or end (+) of a period. You need to combine the C with the period that you want to move through. For instance W+CM will take you to the end of the current month that the work date is in. W-CY would take you to the start of the year that the work date is in.

If you want to know more about advanced date calculations then it’s covered in the next section.

Advanced Date Filters

If your financial year is different to the calendar year then you need to use the Period date filters. These are simply the letter P followed by the period number. For instance, P3 will give you a date filter for the whole of your 3rd period. This means that if your financial year uses calendar months and starts on 01/04/2024 you will be given a filter of 01/06/2024..30/06/2024. The period is calculated based on your work date. If you change your work date to a date in a prior financial year then it will give you the period dates from that year.

When you start to understand how the date formulas work you can begin building more complex examples. Here are a few formulas to get you started:

• • W+1M = A date 1 month after the work date

• • W-1Y = A date 1 year before the work date

• • W+1M+CM = The last day of next month from work date

• • ..W+CM = Up to the end of the current work date month

• • W+1M-CM..W+1M+CM = The whole of next month after work date

• • W-1M-CM..W-1M+CM = The whole of last month from work date

• • P1..P12 = This financial year

• • P1-1Y..P12-1Y = The last financial year

• • ..P12+CM-1Y = Up to the last day of the last financial year

There is a section in the video that explains Business Central date formulas and date calculations visually.

Saved Views with Date Calculations

Once you have mastered data calculations then you want to know how to use them efficiently. You can do this by using saved views in Business Central.

When you have built the filters that you want in a list use the Save As from the assist edit to save a copy of the view. Make sure you give it a sensible name when you save it.

If you save a view with date calculations in it, then every time you open the view the filter will update based on the system or work date (depending on which you have used in the filter).

These saved views will only be visible to you, however, if you customise a profile it will allow the view to be visible to people using that profile.