Others

How to Count Mondays Between Two Dates in Microsoft Access

Tauseeq Magsi Sep 27, 2026 868 views

Question details

The user needs to calculate the total number of Mondays (or any specific weekday) that fall within a defined date range in Microsoft Access.

How to Count Mondays Between Two Dates in Microsoft Access

Product: Microsoft Access
Device & OS: not provided
Scenario: Building a query, report, or form in Microsoft Access that requires calculating the frequency of a specific day of the week between a start date and an end date.
Observed behavior: Calculating the days directly with functions like Day() returns errors or incorrect data; the goal is to correctly use DCount combined with Weekday() to filter and count the specific days.

Before you start

Ensure you have a populated calendar table in your Access database that contains a continuous sequence of dates covering your target date range.

Solution 1Recommended

Use the DCount and Weekday Functions with a Calendar Table

The most reliable method to count specific weekdays in Access is by querying a dedicated calendar table using the DCount function paired with the Weekday function.

The Weekday() function in Access evaluates a date and returns a number from 1 (Sunday) to 7 (Saturday). By setting the criterion to Weekday() = 2, you specifically target Mondays.

To perform the count, you must reference a table that holds every individual date (commonly called a calendar table) and use the Between operator to define the start and end dates.

Set up your Calendar Table

Ensure you have a table named (for example, tblColDt) with a date field (such as PlayDt) containing consecutive dates for the years you are analyzing.

Identify the Start and End Date sources

Locate the form controls or query parameters that define your date range, such as [Forms]![frmCriBal]![txtReqDt] for the start date and [Forms]![frmCriBal]![txtEndDt] for the end date.

Write the DCount expression

Use the following expression in your query, VBA code, or text box Control Source: =DCount("[PlayDt]", "tblColDt", "Weekday([PlayDt]) = 2 And [PlayDt] Between [Forms]![frmCriBal]![txtReqDt] And [Forms]![frmCriBal]![txtEndDt]").

Use the DCount and Weekday Functions with a Calendar Table

Avoid using the Day() function: Using Day([PlayDt]) = 2 will return the 2nd day of the month, not Monday. Always use the Weekday() function to identify days of the week.

Free Microsoft Office alternative

Looking for a Lightweight Alternative for Your Daily Data Tasks?

While Microsoft Access is built for complex relational databases, you can manage daily data tracking, date calculations, and reporting much more easily with WPS Spreadsheet. WPS Office is a free, lightweight, and fully compatible alternative to Microsoft Office, allowing you to perform advanced date math without needing a dedicated calendar table.

1. Open WPS Spreadsheet: Download and launch WPS Office, then create a new Spreadsheet.
2. Input your date range: Enter your start date in cell A1 and your end date in cell B1.
3. Use the NETWORKDAYS.INTL function: In cell C1, type the formula =NETWORKDAYS.INTL(A1, B1, "0111111") to instantly count only the Mondays between the two dates, entirely bypassing the need for a calendar table.

Free and lightweight office suite for daily productivityFully compatible with Microsoft Excel (.xlsx) formatsFamiliar user interface for a seamless migrationPowerful built-in functions for complex date and time calculations

Frequently Asked Questions

Why does my expression with Day([PlayDt])=2 return wrong data?

The Day() function extracts the numerical day of the month (from 1 to 31) from a given date. To evaluate the day of the week (e.g., Monday), you must use the Weekday() function instead.

What number corresponds to Friday in the Weekday function?

By default, the Weekday function starts on Sunday as 1. Therefore, Monday is 2, Tuesday is 3, Wednesday is 4, Thursday is 5, Friday is 6, and Saturday is 7.

Do I absolutely need a calendar table to count weekdays in Access?

While you can write complex VBA custom functions to loop through dates mathematically and count specific days, using a calendar table combined with the DCount function is the most efficient and query-friendly method in Microsoft Access.

How can I count Tuesdays and Thursdays together in the same expression?

You can modify your DCount criteria to use the IN operator. Change the Weekday condition to: Weekday([PlayDt]) In (3, 5).

Office Quick Solutions