|You can't just simply change the date format as you'd like because Excel doesn't know anything about any dates prior to 1/1/1900. The year 1390 means nothing to Excel.|
After spending some time doing some Google searches, I think I've come up with a method that might work for you.
Since Excel is used to working with dates based on the Gregorian calendar, I would convert your Persian dates to Gregorian dates. Once that's done, Excel should have no problem dealing with your date calculations.
The User Defined Function (UDF) shown below will convert Persian dates in the format of yyyy/mm/dd to a Gregorian date and display it as a date in any of the "standard" Excel date formats that you choose.
The UDF actually returns the Serial number for the Gregorian date (which is how Excel stores dates internally) and then displays it in the date format that you choose.
Please note that I did not write the original version of this UDF. I merely modified a UDF that I found at the following site which was written to return the Julian Day Number of a Persian date.
Now, since everyone knows that the Julian Day Number is 2415018.5 higher than the Excel Serial number for a given date, all we have to do is subtract 2415018.5 from the JDN and we'll have the Excel Serial number for the date we are looking for.
That's a joke...I had no idea that Julian Day Numbers even existed before I started looking into this. ;-) However, the part about needing to subtract 2415018.5 is true.
1 - Open the workbook that contains the Persian dates.
2 - Open the VBA Editor with Alt-F11
3 - In the Left hand pane, click on the VBAProject that contains the name of the workbook that contains the Persian dates.
4 - Click Insert...Module
5 - Paste the code posted below into the pane that opens
6 - With a date in the format of 1390/01/30 (yyyy/mm/dd) in A1, enter this any other cell:
The function should return either a Date (if the cell is formatted as a Date) or a number (~5 digits) if not. If you get the number, format the cell as a Date.
Of course, you don't need to actually see the Gregorian Dates. You can calculate the number of days between 2 Persian dates directly by simply using the UDF as follows:
=Greg_Date(A1) - Greg_Date(B1)
assuming A1 contains the later date.
Function Greg_Date(perDate) As Long
Const PERSIAN_EPOCH = 1948321 ' The JDN of 1 Farvardin 1
Dim epbase As Long
Dim epyear As Long
Dim mdays As Long
iYear = Left(perDate, 4)
iMonth = Mid(perDate, 6, 2)
iDay = Right(perDate, 2)
If iYear >= 0 Then
epbase = iYear - 474
epbase = iYear - 473
epyear = 474 + (epbase Mod 2820)
If iMonth <= 7 Then
mdays = (CLng(iMonth) - 1) * 31
mdays = (CLng(iMonth) - 1) * 30 + 6
persian_jdn = CLng(iDay) _
+ mdays _
+ Fix(((epyear * 682) - 110) / 2816) _
+ (epyear - 1) * 365 _
+ Fix(epbase / 2820) * 1029983 _
+ (PERSIAN_EPOCH - 1)
Greg_Date = persian_jdn - 2415018.5
Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.