Date Formula in Business Central
If you’re using NAV/ Dynamics 365 Business Central, you’ll hear about ‘Date Formulas.’ It’s a short mix of signs, letters, and numbers to figure out dates. People use it to find Due Dates or the next date for tasks like Job Queue and Recurring Journals. Mastering these date formulas can empower you to efficiently plan and manage your time-sensitive activities with precision.
These formulas can be up to 32 characters long, including signs, numbers, and letters. Here’s what each letter in the formulas means:
- C: Current (end of)
- D: Day
- W: Week
- WD: Week Day (not a workday). Week Day 1 is Monday.
- M: Month
- Q: Quarter
- Y: Year
Let’s explore some examples of business central dateformula what you can do with it.
| Date | Date Formula | Result | Explanation |
|---|---|---|---|
| 5 Jan 2023 (Fri) | CW | 8 Jan 2023 | End of the current week (Sunday) |
| 5 Jan 2023 (Fri) | WD1 | 9 Jan 2023 | The first Monday after the current date |
| 5 Jan 2023 (Fri) | WD6 | 7 Jan 2023 | The next Saturday after the current date |
| 5 Jan 2023 | D7 | 12 Jan 2023 | The 7th day after the current date |
| 5 Jan 2023 | 7D | 12 Jan 2023 | One week later from the current date |
| 5 Jan 2023 | D25 | 30 Jan 2023 | The 25th day after the current date |
| 5 Jan 2023 | -D10 | 26 Dec 2022 | The 10th day before the current date |
| 5 Jan 2023 | -CM | 1 Jan 2023 | Beginning of the current month |
| 5 Jan 2023 | CM | 31 Jan 2023 | End of the current month |
| 5 Jan 2023 | 1M | 5 Feb 2023 | One month later from the current date |
| 5 Jan 2023 | 1M+CM | 31 Jan 2023 | End of the next month |
| 5 Jan 2023 | CM+D25 | 25 Feb 2023 | The 25th day of the next month |
| 5 Jan 2023 | CM+45D | 15 Mar 2023 | 45 days after the end of the current month |
| 5 Jan 2023 | 1Q+1M+CM | 30 Apr 2023 | One quarter and one month later, end of month |
| 5 Jan 2023 | 1Y-CY | 1 Jan 2024 | Beginning of the next year |
| 5 Jan 2023 | CY+1D | 1 Jan 2024 | Beginning of the next year |
| 30 Jan 2023 | 1M | 28 Feb 2023 | One month later (February 2023 only has 28 days) |
Hope you get an idea of how to use date formulas in CalcFormula Business Central.
Date Formats In Navision
Date can be entered into Microsoft Dynamics NAV (Navision) with the following code:
- w : workdate
- t : system date
- c: closing date
- # : number of days
- m : Monday
- tu : Tuesday
- we : Wednesday
- th: Thursday
- f : Friday
- sa: Saturday
- su : Sunday
