Minimum Version Required: 26.02
Pre-Conditions
Administration: To adjust calculation logic (such as Week Numbering or Business Day definitions), the user must be a System Administrator with access to System Settings.
Steps
Overview
The Date Conversions table serves as the system's central calendar reference. It is automatically populated by the system (up to 10 years in advance) and is primarily used as a data source for Reports to aggregate data by specific timeframes (e.g., Trimester, Fiscal Week, or Selling Day).
Viewing Date Conversion Data
To validate current date logic or look up specific conversion values:
Using Universal Search, navigate to the Date Conversions table.
Review the columns to understand how a specific Date maps to a Week, Quarter, or Business Day status.
Configuring Week Numbering (Setting 3431)
The specific method used to calculate "Week" and "Day of Week" is controlled by System Setting 3431.
This setting resolves discrepancies between different global standards (e.g., Excel vs. ISO).
Option 0: ISO 8601 (International Standard)
First Day of Week: Monday.
Week 1 Definition: The first week of the year is the week that contains January 4th (or the first week with at least 4 days in the new year).
Behavior: This is the international business standard. It avoids split weeks but can result in the first few days of January falling into the last week of the previous year's numbering.
Learn More: ISO 8601 Week Dates (Wikipedia)
Option 1: North American / Excel Standard
First Day of Week: Sunday.
Week 1 Definition: The first week of the year is the week that contains January 1st.
Behavior: This aligns with standard US calendars and Microsoft Excel's default "System 1" week numbering. It ensures that January 1st is always in Week 1.
Learn More: Week Numbering Systems (Excel Functions)
Note: Changing this setting will update the Week, DayOfWeek, YearWeek, and YearQuarter columns during the next weekly recalculation.
Configuring Business Days (Setting 341)
The Is Business Day column is calculated based on System Setting 341.
This determines which weekend days are considered working days:
0: No weekend business (Monday-Friday only).
1: Saturday is a business day.
2: Sunday is a business day.
3: Both Saturday and Sunday are business days.
Using Date Conversions in Reporting
When building custom reports, join the Date Conversions table to your transaction table (e.g., Sales or Inventory) using the Date column.
Example: To create a report showing "Sales by Trimester," join your Sales table to Date Conversions, and group your results by the Trimester column.
Fields And Definitions
Date: The standard calendar date (Primary Key).
Is Business Day: A boolean flag (1 or 0) indicating if the date is a working day based on System Setting 341.
Week: The week number (1-53) of the year. The calculation (Monday-start vs. Sunday-start) depends on Setting 3431.
Day Of Week: The numeric day of the week. If using ISO 8601, Monday = 1. If using North American, Sunday = 1.
Trimester: Breaks the year into three fixed 4-month periods... 1: January - April; 2: May - August; 3: September - December
Selling Day of Month: A sequential counter of Business Days within a specific month. (e.g., If the 1st is a Sunday and non-business, the 2nd (Monday) is Selling Day 1).
Phase: Represents a 4-week cycle calculation used for specific delivery or inventory rotations.
Forecast Year / Forecast Week: These columns are calculated based on Setting 56 (Weekly Forecast Start Date) and allow for forecasting logic that differs from the standard calendar week.
Best Practices
Consistency is Key: Avoid changing Setting 3431 (Week Mode) in the middle of a fiscal year, as this can cause reporting discontinuities (e.g., data shifting from Week 10 to Week 11 unexpectedly).