"excel + Date" Problem

January 1, 2012 at 08:21:30
Specs: Windows 7 & Mac OS X, core i5
help me please
i have dates in Persian format (e.g. 1390/01/30)
i cant calculate number of days between dates; because EXCEL doent know my date format.
i decided to change my date format (e.g. to 30/1/1390)
but i cant? is there any expert?!

See More: "excel + Date" Problem

Report •

January 1, 2012 at 15:43:03
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.

Good luck!

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
       End If
     epyear = 474 + (epbase Mod 2820)
       If iMonth <= 7 Then
         mdays = (CLng(iMonth) - 1) * 31
         mdays = (CLng(iMonth) - 1) * 30 + 6
       End If
    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
End Function

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Report •

January 4, 2012 at 03:33:24
tank U dear DerbyDad03... so much :-)
alternative method:
in minitab statistical software we can define date formats as we want, simply!

Report •

Related Solutions

Ask Question