How to Fix Incorrect Hijri Date Displayed in Microsoft Access
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.

- 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.
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.
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.
Open the affected table or form in Design View and locate the Hijri date field to check how the data is being captured.
Temporarily change the field's display format to 'General Date' to reveal if a time value (the decimal portion) is attached to the date.
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.
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.

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. Download the Software: Visit the official WPS Office website and click the free download button.
- 2. Install WPS Office: Run the lightweight installer to quickly set up the suite on your device.
- 3. Open WPS Spreadsheet: Launch the Spreadsheet application to easily manage, calculate, and format your date-related data.

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.




