Others

How to Fix Incorrect Hijri Date Displayed in Microsoft Access

Steve KSteve K Nov 2, 2026 868 views

Question details

The user needs to resolve a discrepancy where Microsoft Access displays a Hijri date that is one day off from the expected value.

How to Fix an Incorrect Hijri Date Displayed in Microsoft Access
Product
Microsoft Access
Device & OS
not provided
Scenario
Viewing or generating dates in the Hijri calendar format within a Microsoft Access database.
Observed behavior
The displayed Hijri date differs by one day from the actual calendar value, likely due to stored time fractions or UTC conversions.
Before you start

Verify if the date fields in your database were generated using a function that includes the current time (such as Now()) rather than just the date (such as Date()), as this time component often causes calendar shifts.

Solution 1Recommended

Remove Time Components and Correct UTC Conversions

Identify the underlying double-precision number stored in Access and remove unintended time decimals to prevent lunar calendar shifts.

Microsoft Access stores dates as double-precision numbers. The integer portion represents the days since December 30, 1899, while the decimal portion represents the time of day.

Because the Hijri calendar follows the lunar cycle, time fractions (especially when combined with UTC conversions) can easily push the calculated date into the next or previous day.

1
Review the Input Method

Open the affected table or form in Design View and locate the Hijri date field to check how the data is being captured.

2
Inspect the Underlying Value

Temporarily change the field's display format to 'General Date' to reveal if a time value (the decimal portion) is attached to the date.

3
Update the Default Value Function

If the date is auto-generated using the Now() function, update the Default Value property to use the Date() function instead, ensuring no time fraction is recorded.

4
Adjust UTC Conversions

If a VBA module or query converts the date using UTC, adjust your code to compare the underlying local date with the expected lunar date before applying the final display format.

Remove Time Components and Correct UTC Conversions
Formatting vs. Stored Value: Applying a different format in Access only changes how the date appears visually. It does not alter the underlying numeric value stored in the database.
Free Microsoft Office alternative

Manage Data and Dates Easily with a Lightweight Office Alternative

While Microsoft Access is a specialized tool for complex relational databases, many everyday data tracking and date calculation tasks can be handled far more easily in a spreadsheet. WPS Office offers a free, lightweight alternative that simplifies data management without the steep learning curve.

  1. 1. Download the Software: Visit the official WPS Office website and click the free download button.
  2. 2. Install WPS Office: Run the lightweight installer to quickly set up the suite on your device.
  3. 3. Open WPS Spreadsheet: Launch the Spreadsheet application to easily manage, calculate, and format your date-related data.
Seamless compatibility with Microsoft Office formats, including .xlsx for spreadsheet dataFree and lightweight design for quick installation and smooth operationFamiliar user interface ensuring zero learning curve for Office usersRobust built-in formulas for managing complex date conversions and calculations
microsoft office alternative - wps office

Frequently Asked Questions

Why didn't changing the date format fix my Hijri calendar issue in Access?

Formatting only changes the visual representation of the date on your screen. The actual data stored in Access remains a double-precision number, so any underlying time fractions causing the one-day shift are still present.

How are dates actually stored in Microsoft Access?

Dates are stored as numeric values. The integer part counts the number of days elapsed since December 30, 1899, while the decimal fraction represents the specific time of day.

Can UTC time cause a one-day discrepancy in lunar calendars?

Yes. The Hijri calendar is based on the lunar cycle, whereas UTC is based on Earth's rotation. Applying UTC offsets to local times can shift the timestamp across midnight, resulting in the Hijri date calculating one day off.