If you need to convert Gregorian dates to Persian (Jalali) dates in Excel or Access VBA, or convert Persian dates back to Gregorian, this tutorial provides a complete VBA module that you can add to your project.
The two main functions are JalaliCalendar, which converts a Gregorian Date to a Persian date, and GregorianCalendar, which converts a Persian date in YYYYMMDD format to a Gregorian date. The module also includes functions for Persian leap-year detection and calculating the number of days between two Persian dates.
Note: This implementation uses 1 Farvardin 1300 (21 March 1921) as its base date and is designed for Persian dates from the year 1300 onward.
Convert a Gregorian Date to Persian in VBA
Use the JalaliCalendar function to convert a Gregorian date to a Persian date. Its first argument is a VBA Date, while the second argument determines the output format.
Sub TestGregorianToJalali()
Dim result As Variant
result = JalaliCalendar(DateSerial(2024, 9, 5), jcNumber)
MsgBox result
End SubThe result is:
14030615In other words, September 5, 2024 is converted to 15 Shahrivar 1403.
To receive a readable string containing the weekday and Persian month name, use jcString:
Sub TestGregorianToJalaliString()
Dim result As Variant
result = JalaliCalendar(DateSerial(2024, 9, 5), jcString)
MsgBox result
End SubThe English version of the module returns:
Thursday 15 Shahrivar 1403Convert a Persian (Jalali) Date to Gregorian in VBA
Use the GregorianCalendar function to convert a Persian date to Gregorian. The Persian input date must be supplied as an eight-digit number in YYYYMMDD format.
For example, 15 Shahrivar 1403 is entered as 14030615:
Sub TestJalaliToGregorian()
Dim gregorianDate As Date
gregorianDate = GregorianCalendar(14030615, True)
MsgBox Format(gregorianDate, "yyyy-mm-dd")
End SubThe result is:
2024-09-05How to Add the Date Conversion Module to Excel or Access
Copy the complete module provided later in this tutorial and add it to a standard VBA module in your Excel or Access project.
- Press
Alt + F11in Excel or Access to open the Visual Basic Editor. - Choose
Insert → Module. - Paste the complete module code into the new module.
- Save the project. In Excel, use a macro-enabled format such as
.xlsm. - You can now call
JalaliCalendar,GregorianCalendar, andJCDateDifffrom your VBA code.
Function Inputs and Output Formats
JalaliCalendar
The JalaliCalendar function receives a Gregorian VBA Date:
JalaliCalendar(InputDate As Date, Optional returnValueType As jCalendar_returnValueType = jcString)The optional returnValueType argument determines the format of the result:
jcString: readable output such asThursday 15 Shahrivar 1403.jcNumber: a value inYYYYMMDDformat such as14030615.jcSplitArray: a delimited string containing day, month and year, which can then be converted to an array with VBA’sSplitfunction.
For example:
Dim arrDate() As String
arrDate = Split(JalaliCalendar(Date, jcSplitArray), "!")
' arrDate(0) = day
' arrDate(1) = month
' arrDate(2) = yearGregorianCalendar
The GregorianCalendar function receives the Persian date as a numeric value:
GregorianCalendar(inJalaliDate As Long, jalaliDateToGregorian As Boolean)To perform a direct Persian-to-Gregorian conversion, pass True as the second argument:
GregorianCalendar(14030615, True)How the Persian–Gregorian Date Conversion Works
The module uses a known base date: 1 Farvardin 1300 is mapped to 21 March 1921. When converting a Gregorian date to Persian, the module first calculates the number of elapsed days from that base date and then converts those days into a Persian year, month and day.
A separate arithmetic function is used to identify Persian leap years. Leap-year detection is important because it determines whether Esfand contains 29 or 30 days.
Output Type Definitions
The module defines an Enum to control the output format of JalaliCalendar:
Public Enum jCalendar_returnValueType
jcSplitArray = 1
jcNumber = 2
jcString = 3
End EnumDetecting a Persian Leap Year with JalaliKabise
The JalaliKabise function uses an arithmetic calculation to determine whether the supplied Persian year is a leap year:
Public Function JalaliKabise(Yr As Integer) As Boolean
calcYear = (Yr + 2346) * 0.24219858156
calcYear = calcYear - Int(calcYear)
If calcYear < 0.24219858156 Then
JalaliKabise = True
Else
JalaliKabise = False
End If
End FunctionHow JalaliCalendar Converts Gregorian Dates to Persian
The function begins by calculating the number of days between the Gregorian input date and the base date:
JCDaysCount = DateDiff("d", #3/21/1921#, InputDate) + 1
JCYear = 1300Complete Persian years are then subtracted from the elapsed-day count. The module checks each year to determine whether it contains 365 or 366 days.
Do Until JCDaysCount < 365
If JalaliKabise(JCYear) = True Then
D = 366
Else
D = 365
End If
If JCDaysCount - D > 0 Then
JCDaysCount = JCDaysCount - D
JCYear = JCYear + 1
Else
Exit Do
End If
LoopOnce the Persian year has been identified, the remaining days are converted to the appropriate Persian month and day.
How GregorianCalendar Converts Persian Dates to Gregorian
The GregorianCalendar function first extracts the day, month and year from the eight-digit Persian date:
GCJalaliDay = Right(inJalaliDate, 2)
GCJalaliMonth = Mid(inJalaliDate, 5, 2)
GCJalaliYear = Left(inJalaliDate, 4)The function then validates the month and day values, including the special rules for Esfand in leap and non-leap years.
Finally, the number of elapsed Persian-calendar days is added to the Gregorian base date:
GCcalculateDate = DateAdd("d", GCDaysCount - 1, #3/21/1921#)Calculate the Difference Between Two Persian Dates
The JCDateDiff function converts both Persian dates to Gregorian dates and then uses VBA’s DateDiff function to calculate the number of days between them.
Public Function JCDateDiff(firstDate As Long, secoundDate As Long)
JCDateDiff = DateDiff("d", _
GregorianCalendar(firstDate, True), _
GregorianCalendar(secoundDate, True), _
vbSaturday)
End FunctionDisplay Today’s Date in the Persian Calendar
Sub ShowTodayJalali()
Dim todayJalali As String
todayJalali = JalaliCalendar(Date, jcString)
MsgBox "Today: " & todayJalali
End SubComplete VBA Module for Persian and Gregorian Date Conversion
Copy the complete code below into a standard VBA module. In this English version, jcString returns English weekday names and transliterated Persian month names. Numeric date conversion works the same way as in the Persian version.
Option Explicit
'==============================================================
' Module purpose:
' Convert Persian (Jalali) dates to Gregorian and vice versa
'
' Persian base date: 01-01-1300
' Gregorian base date: 21-03-1921
' Designed range: Persian dates from year 1300 onward
'==============================================================
Dim calcYear As Variant
Dim D As Integer
Dim JCDaysCount As Long
Dim JCMonth As Integer
Dim JCYear As Integer
Dim JCRemainDays As Integer
Dim bytDayOfWeek As Byte
Dim strFaMonth As String
Dim strFaDay As String
Dim GCYear As Integer
Dim GCDaysCount As Long
Dim GCMonth As Integer
Dim GCYearNow As Integer
Dim GCDayNow As Integer
Dim GCMonthNow As Integer
Dim GCJalaliNow As String
Dim arrGCJalaliNow() As String
Dim GCcalculateDate As Date
Dim GCJalaliDay As Integer
Dim GCJalaliMonth As Integer
Dim GCJalaliYear As Integer
Dim strJCMonth As String
Dim strJCRemainDays As String
Public Enum jCalendar_returnValueType
jcSplitArray = 1
jcNumber = 2
jcString = 3
End Enum
Public Function JalaliKabise(Yr As Integer) As Boolean
calcYear = (Yr + 2346) * 0.24219858156
calcYear = calcYear - Int(calcYear)
If calcYear < 0.24219858156 Then
JalaliKabise = True
Else
JalaliKabise = False
End If
End Function
'==============================================================
' Convert a Gregorian date to a Persian date.
'
' InputDate:
' Gregorian VBA Date.
'
' returnValueType:
' jcString = readable text
' jcNumber = YYYYMMDD
' jcSplitArray = day!month!year string for use with Split()
'==============================================================
Public Function JalaliCalendar( _
InputDate As Date, _
Optional returnValueType As jCalendar_returnValueType = jcString _
) As Variant
JCDaysCount = DateDiff("d", #3/21/1921#, InputDate) + 1
JCYear = 1300
Do Until JCDaysCount < 365
If JalaliKabise(JCYear) = True Then
D = 366
Else
D = 365
End If
If JCDaysCount - D > 0 Then
JCDaysCount = JCDaysCount - D
JCYear = JCYear + 1
Else
Exit Do
End If
Loop
JCRemainDays = JCDaysCount
If JCRemainDays <= 31 Then
JCMonth = 1
End If
If JCRemainDays > 31 And JCRemainDays <= 186 Then
JCMonth = 1
Do Until JCRemainDays <= 31
JCRemainDays = JCRemainDays - 31
JCMonth = JCMonth + 1
Loop
End If
If JCRemainDays > 186 And JCRemainDays <= 336 Then
JCMonth = 7
JCRemainDays = JCRemainDays - 186
Do Until JCRemainDays <= 30
JCRemainDays = JCRemainDays - 30
JCMonth = JCMonth + 1
Loop
End If
If JCRemainDays > 336 And JCRemainDays < 365 Then
JCMonth = 12
JCRemainDays = JCRemainDays - 336
End If
If JCRemainDays = 365 Then
JCMonth = 12
JCRemainDays = 29
End If
If JCRemainDays = 366 Then
If JalaliKabise(JCYear) = True Then
JCMonth = 12
JCRemainDays = 30
Else
JCMonth = 1
JCRemainDays = 1
JCYear = JCYear + 1
End If
End If
bytDayOfWeek = Format(InputDate, "w", vbSaturday)
If returnValueType = jcSplitArray Then
JalaliCalendar = JCRemainDays & "!" & JCMonth & "!" & JCYear
Exit Function
End If
If returnValueType = jcNumber Then
If JCMonth < 10 Then
strJCMonth = 0 & JCMonth
Else
strJCMonth = JCMonth
End If
If JCRemainDays < 10 Then
strJCRemainDays = 0 & JCRemainDays
Else
strJCRemainDays = JCRemainDays
End If
JalaliCalendar = JCYear & strJCMonth & strJCRemainDays
Exit Function
End If
Select Case JCMonth
Case 1
strFaMonth = "Farvardin"
Case 2
strFaMonth = "Ordibehesht"
Case 3
strFaMonth = "Khordad"
Case 4
strFaMonth = "Tir"
Case 5
strFaMonth = "Mordad"
Case 6
strFaMonth = "Shahrivar"
Case 7
strFaMonth = "Mehr"
Case 8
strFaMonth = "Aban"
Case 9
strFaMonth = "Azar"
Case 10
strFaMonth = "Dey"
Case 11
strFaMonth = "Bahman"
Case 12
strFaMonth = "Esfand"
End Select
Select Case bytDayOfWeek
Case 1
strFaDay = "Saturday"
Case 2
strFaDay = "Sunday"
Case 3
strFaDay = "Monday"
Case 4
strFaDay = "Tuesday"
Case 5
strFaDay = "Wednesday"
Case 6
strFaDay = "Thursday"
Case 7
strFaDay = "Friday"
End Select
If returnValueType = jcString Then
JalaliCalendar = strFaDay & " " & _
JCRemainDays & " " & _
strFaMonth & " " & _
JCYear
End If
End Function
'==============================================================
' Convert a Persian date to Gregorian.
'
' inJalaliDate:
' Persian date in YYYYMMDD format.
' Example: 14030615
'
' For direct Persian-to-Gregorian conversion:
' jalaliDateToGregorian = True
'==============================================================
Public Function GregorianCalendar( _
inJalaliDate As Long, _
jalaliDateToGregorian As Boolean _
) As Variant
GCJalaliDay = Right(inJalaliDate, 2)
GCJalaliMonth = Mid(inJalaliDate, 5, 2)
GCJalaliYear = Left(inJalaliDate, 4)
' Basic validation
If GCJalaliDay > 31 Or GCJalaliMonth > 12 Then
GregorianCalendar = "False"
MsgBox "The input date is invalid.", vbCritical
Exit Function
End If
If GCJalaliDay = 0 Or _
GCJalaliMonth = 0 Or _
GCJalaliYear < 1300 Then
GregorianCalendar = "False"
MsgBox "The input date is invalid.", vbCritical
Exit Function
End If
If GCJalaliMonth > 6 Then
If GCJalaliDay > 30 Then
GregorianCalendar = "False"
MsgBox "The input date is invalid.", vbCritical
Exit Function
End If
End If
If GCJalaliMonth = 12 Then
If JalaliKabise(GCJalaliYear) = False Then
If GCJalaliDay > 29 Then
GregorianCalendar = "False"
MsgBox "The number of days in Esfand is invalid.", vbCritical
Exit Function
End If
End If
End If
' Calculate elapsed days from the beginning of year 1300
GCDaysCount = 0
GCYear = 1300
Do Until GCYear = GCJalaliYear
If JalaliKabise(GCYear) = True Then
D = 366
Else
D = 365
End If
GCDaysCount = GCDaysCount + D
GCYear = GCYear + 1
Loop
' Convert elapsed months to days
GCMonth = GCJalaliMonth - 1
If GCMonth <> 0 Then
Select Case GCMonth
Case 1
GCDaysCount = GCDaysCount + 31
Case 2
GCDaysCount = GCDaysCount + 62
Case 3
GCDaysCount = GCDaysCount + 93
Case 4
GCDaysCount = GCDaysCount + 124
Case 5
GCDaysCount = GCDaysCount + 155
Case 6
GCDaysCount = GCDaysCount + 186
Case 7
GCDaysCount = GCDaysCount + 216
Case 8
GCDaysCount = GCDaysCount + 246
Case 9
GCDaysCount = GCDaysCount + 276
Case 10
GCDaysCount = GCDaysCount + 306
Case 11
GCDaysCount = GCDaysCount + 336
End Select
End If
GCDaysCount = GCDaysCount + GCJalaliDay
GCcalculateDate = DateAdd( _
"d", _
GCDaysCount - 1, _
#3/21/1921# _
)
If jalaliDateToGregorian = True Then
GregorianCalendar = GCcalculateDate
Exit Function
End If
' Convert the current date to Persian
GCJalaliNow = JalaliCalendar(Now, jcSplitArray)
arrGCJalaliNow = Split(GCJalaliNow, "!")
GCDayNow = arrGCJalaliNow(0)
GCMonthNow = arrGCJalaliNow(1)
GCYearNow = arrGCJalaliNow(2)
' Reject future dates in this mode
If GCYearNow < GCJalaliYear Then
MsgBox "A future date cannot be entered.", vbCritical
GregorianCalendar = "False"
Exit Function
End If
If GCYearNow = GCJalaliYear Then
If GCMonthNow < GCJalaliMonth Then
MsgBox "A future date cannot be entered.", vbCritical
GregorianCalendar = "False"
Exit Function
End If
End If
If GCYearNow = GCJalaliYear Then
If GCMonthNow = GCJalaliMonth Then
If GCDayNow < GCJalaliDay Then
MsgBox "A future date cannot be entered.", vbCritical
GregorianCalendar = "False"
Exit Function
End If
End If
End If
GregorianCalendar = JalaliCalendar( _
GCcalculateDate, _
jcString _
)
End Function
'==============================================================
' Calculate the number of days between two Persian dates
'==============================================================
Public Function JCDateDiff( _
firstDate As Long, _
secoundDate As Long _
)
JCDateDiff = DateDiff( _
"d", _
GregorianCalendar(firstDate, True), _
GregorianCalendar(secoundDate, True), _
vbSaturday _
)
End FunctionVideo Tutorial: Using the Module in Excel
The following video demonstrates how to transfer the module to Excel and use its Persian–Gregorian date conversion functions.
Frequently Asked Questions
How do I convert a Gregorian date to Persian in VBA?
Pass a VBA Date value to JalaliCalendar. For example, JalaliCalendar(Date, jcNumber) converts today’s system date to a Persian date in YYYYMMDD format.
What format should I use for a Persian input date?
GregorianCalendar expects an eight-digit Persian date in YYYYMMDD format. For example, 15 Shahrivar 1403 is entered as 14030615.
Does this VBA code work in both Excel and Access?
Yes. The module can be added to a standard VBA module in both Microsoft Excel and Microsoft Access projects.
Does jcSplitArray return an actual array?
No. Despite its name, this mode returns a day!month!year string. Use VBA’s Split function to convert that string into an array.
Does this module support Persian dates before 1300?
This implementation is based on 1 Farvardin 1300 and the Persian-to-Gregorian function treats years earlier than 1300 as invalid.
Summary
This tutorial provides a complete VBA solution for converting Gregorian dates to Persian (Jalali) dates and Persian dates back to Gregorian. JalaliCalendar performs Gregorian-to-Persian conversion, while GregorianCalendar converts a Persian YYYYMMDD value to a Gregorian VBA date.
The module also includes Persian leap-year detection and the JCDateDiff function for calculating the number of days between two Persian dates, making it a useful base for Persian date handling in Excel and Access VBA projects.