Subtracting Dates VBA

  • Thread starter Thread starter Jean-Jerome Doucet via OfficeKB.com
  • Start date Start date
J

Jean-Jerome Doucet via OfficeKB.com

Hi,

I want to subtract two dates 'YYYYY-MM-DD' but by doing in VBA and pointing
to their respective cells and dump the result in another cell.

For example, I am working on this experiment :

Sub datescalcul()
Dim Baux As Double
Baux = Application.DATEDIF(Worksheets("Formulaire").Range("I16").Value,
Worksheets("Formulaire").Range("J16").Value, "m") / 12
sheets("Formulaire").Range("K16").Value = Baux
End Sub
Click to expand...

But it does not work. Maye it is that dateDif Excel function cannot be
converted in VBA function?
Thx!

JJD
 
"does not work" does not give much information.
But in VBA, it is DATEDIFF (2 F's)

Niek Otten
Jean-Jerome Doucet via OfficeKB.com said:
Hi,

I want to subtract two dates 'YYYYY-MM-DD' but by doing in VBA and
pointing
to their respective cells and dump the result in another cell.

For example, I am working on this experiment :

Sub datescalcul()
Dim Baux As Double
Baux = Application.DATEDIF(Worksheets("Formulaire").Range("I16").Value,
Worksheets("Formulaire").Range("J16").Value, "m") / 12
sheets("Formulaire").Range("K16").Value = Baux
End Sub
Click to expand...

But it does not work. Maye it is that dateDif Excel function cannot be
converted in VBA function?
Thx!

JJD
Click to expand...
 
So that is without "Application."

Niek Otten said:
"does not work" does not give much information.
But in VBA, it is DATEDIFF (2 F's)

Niek Otten
Jean-Jerome Doucet via OfficeKB.com said:
Hi,

I want to subtract two dates 'YYYYY-MM-DD' but by doing in VBA and
pointing
to their respective cells and dump the result in another cell.

For example, I am working on this experiment :

Sub datescalcul()
Dim Baux As Double
Baux = Application.DATEDIF(Worksheets("Formulaire").Range("I16").Value,
Worksheets("Formulaire").Range("J16").Value, "m") / 12
sheets("Formulaire").Range("K16").Value = Baux
End Sub
Click to expand...

But it does not work. Maye it is that dateDif Excel function cannot be
converted in VBA function?
Thx!

JJD
Click to expand...
Click to expand...
 
You are right, my apologies for the lack of details.

I forgot to mention that the error is on my subtraction. It says property or
method not managed by this object. And I want to return a numer of years and
have decimal or where we are in the last years (e.g. 3 and a half year 3,50).

Here's the way it is supposed to work :
Oh and it is J16 - I16 (upper date - lower date).

Werner
 
Ops wrong name (I use Werner as a nickname on other forums but I use my real
name on this one.)

Jean-Jérôme Doucet
 
It is also a different order

With Worksheets("Formulaire")
Baux = DateDiff("m", .Range("I16").Value, .Range("J16").Value) / 12
End With

--

HTH

RP
(remove nothere from the email address if mailing direct)


Jean-Jerome Doucet via OfficeKB.com said:
Hi,

I want to subtract two dates 'YYYYY-MM-DD' but by doing in VBA and pointing
to their respective cells and dump the result in another cell.

For example, I am working on this experiment :

Sub datescalcul()
Dim Baux As Double
Baux = Application.DATEDIF(Worksheets("Formulaire").Range("I16").Value,
Worksheets("Formulaire").Range("J16").Value, "m") / 12
sheets("Formulaire").Range("K16").Value = Baux
End Sub
Click to expand...

But it does not work. Maye it is that dateDif Excel function cannot be
converted in VBA function?
Thx!

JJD
Click to expand...
 
Thank you a lot Bob!!

I adjusted a bit the code for my needs and i works perfectly!

Have a good day!

Werner

Sub datescalcul()

Dim Baux As Double
Dim I As Long

Let lstRw = Sheets("Formulaire").Range("a65536").End(xlUp).Row

For I = 16 To lstRw
With Worksheets("Formulaire")
Baux = DateDiff("m", Sheets("Formulaire").Range("I" & I).Value, Sheets
("Formulaire").Range("J" & I).Value) / 12
Sheets("Formulaire").Range("K" & I).Value = Baux
End With

Next I


End Sub
Click to expand...
 
Back
Top