CODEWITHSUNDEEP
vbaexcel
M. Safari
M. Safari

Reputation: 313

Using Excel DateAdd Function

I want to use excel DateAdd function to add some days to a cell containing a persian/hijri calendar date. So, I've created a button and assigned a macro to it like :

Sub mydateaddfunction()
    Dim FirstDate As Date
    Dim Number As Integer
      FirstDate = Sheets(3).Range("e13").Value
      Number = Sheets(3).Range("b13").Value
      Sheets(3).Range("e14").Value = DateAdd("d", Number, FirstDate)
End Sub

but unfortunately it throws an error

application defined or object-defined error

error coming from this line :

Sheets(3).Range("e14").Value = DateAdd("d", Number, FirstDate)

e13 cell contains the date, b13 contains the number of days I want to add to the e13 cell. so if e13 cell be something like :

1396/10/17

and b13 contains a number like 3, I want e14 to be:

1396/10/20

What I'm doing wrong?

Upvotes: 0

Views: 1035

Answers (3)

Tahir
Tahir

Reputation: 21

For those looking for a simple solution, they can use Excel existing function called "Workday" to add x number of days to a data.

WORKDAY(start_date, days, [holidays])

Upvotes: 0

Storax
Storax

Reputation: 12167

In case of Excel 2016 Change your code to

Sub mydateaddfunction()
    Dim FirstDate As Date
    Dim Number As Integer

    Dim rg As Range
    Set rg = Union(Sheets(1).Range("E13"), Sheets(1).Range("E14"))
    rg.NumberFormat = "[$-fa-IR,16]dd/mm/yyyy;@"


      FirstDate = Sheets(1).Range("e13").Value
      Number = Sheets(1).Range("b13").Value
      Sheets(1).Range("e14").Value = DateAdd("d", Number, FirstDate)
End Sub

Upvotes: 1

Subodh Tiwari sktneer
Subodh Tiwari sktneer

Reputation: 9976

By default VBA uses the Gregorian Date Calendar. So I assume that you need to convert your Hijri Date to Gregorian Date and after adding the days to it convert the Gregorian Date back to the Hijri Date.

Please give this a try...

Sub mydateaddfunction()
    Dim FirstDate As Date
    Dim Number As Integer
    Dim gDate As Date
    FirstDate = HijriToGreg(Sheets(3).Range("e13").Value)
    Number = Sheets(3).Range("b13").Value
    gDate = DateAdd("d", Number, FirstDate)
    Sheets(3).Range("e14").Value = GregToHijri(gDate)
End Sub

Function GregToHijri(dtGegDate As Date) As String
    'Converts Gregorian date to a Hijri date
    VBA.Calendar = vbCalHijri
    GregToHijri = dtGegDate
    VBA.Calendar = vbCalGreg
End Function

Function HijriToGreg(dtHijDate As String) As Date
    'Converts Hijri date to a Gregorian date
    VBA.Calendar = vbCalHijri
    HijriToGreg = dtHijDate
    VBA.Calendar = vbCalGreg
End Function

Upvotes: 0

Related Questions