Datedif Function

[Senthil Murugan]

DATEDIF FUNCTION

Hi Good afternoon everybody

Can you help to write code for Datedif Function like

Testbox3. value = Datedif(Testbox1.value,Textbox2.value,"ym")
Testbox4. value = Datedif(Testbox1.value,Textbox2.value,"md")
Testbox5. value = Datedif(Testbox1.value,Textbox2.value,"y")


Textbox1.value = 05-08-1985
Textbox2.value = 10-12-2024 ( Today )

The Result should be

39 years, 4 years and 5 days



with regards

Datedif Function

A.Senthil Murugan

 

[Senthil Murugan]

Sorry, typo error
39 years, 4 months and 5 days

 

[HighAndWilder]

DATEDIF FUNCTION

Hi Good afternoon everybody

Can you help to write code for Datedif Function like

Testbox3. value = Datedif(Testbox1.value,Textbox2.value,"ym")
Testbox4. value = Datedif(Testbox1.value,Textbox2.value,"md")
Testbox5. value = Datedif(Testbox1.value,Textbox2.value,"y")


Textbox1.value = 05-08-1985
Textbox2.value = 10-12-2024 ( Today )

The Result should be

39 years, 4 years and 5 days



with regards

Datedif Function

A.Senthil Murugan

Does this work? Not tested using text boxes.

=LET(s, Testbox1.value,e, Testbox2.value,DATEDIF(s,e,"y")&" years, "&DATEDIF(s,e,"ym")&" months, "&DATEDIF(s,e,"md")&" days")

 

[Senthil Murugan]

Does this work? Not tested using text boxes.

=LET(s, Testbox1.value,e, Testbox2.value,DATEDIF(s,e,"y")&" years, "&DATEDIF(s,e,"ym")&" months, "&DATEDIF(s,e,"md")&" days")

Sir, How to use this code

using Evaluate method if yes , how?

Testbox3.value = LET(s, Testbox1.value,e, Testbox2.value,DATEDIF(s,e,"y")&" years, "&DATEDIF(s,e,"ym")&" months, "&DATEDIF(s,e,"md")&" days")
( Not working )

with regards


A.Senthil Murugan

 

[Senthil Murugan]

My Code is

Private Sub CommandButton1_Click()
Dim ss, SS1 As Date
Dim dd, dd1, dd2 As Long
ss = TextBox1.Value
SS1 = TextBox2.Value
TextBox1.Value = Format(Range("A2").Value, "dd-mm-yyyy")
TextBox2.Value = Format(Range("b2").Value, "dd-mm-yyyy")
dd = DateDiff("yyyy", SS1, ss)
dd1 = DateDiff("m", SS1, ss)
dd2 = DateDiff("d", SS1, ss)
TextBox3.Value = Evaluate(dd)
TextBox4.Value = Evaluate(dd1)
TextBox5.Value = Evaluate(dd2)
End Sub

 

[Joe4]

If you are trying to use this in VBA, you should not need the EVALUATE function.
You should be able to just replace all of this:

dd = DateDiff("yyyy", SS1, ss)
dd1 = DateDiff("m", SS1, ss)
dd2 = DateDiff("d", SS1, ss)
TextBox3.Value = Evaluate(dd)
TextBox4.Value = Evaluate(dd1)
TextBox5.Value = Evaluate(dd2)

with this:

TextBox3.Value = DateDiff("yyyy", SS1, ss)
TextBox4.Value = DateDiff("m", SS1, ss)
TextBox5.Value = DateDiff("d", SS1, ss)

See here: MS Excel: How to use the DATEDIFF Function (VBA)

Also note that this does not do what you think it does:

Dim ss, SS1 As Date
Dim dd, dd1, dd2 As Long

SS1 will be declared to be a Date, and dd2 will be declared as Long, but all the others (ss, dd, and dd1) will all be variant.
Each variable needs to be declared explicitly to have your desired effect, either like this:

Dim ss As Date, SS1 As Date
Dim dd As Long, dd1 As Long, dd2 As Long

or like this:

Dim ss As Date
Dim SS1 As Date
Dim dd As Long
Dim dd1 As Long
Dim dd2 As Long

 

[HighAndWilder]

Sir, How to use this code

using Evaluate method if yes , how?

Testbox3.value = LET(s, Testbox1.value,e, Testbox2.value,DATEDIF(s,e,"y")&" years, "&DATEDIF(s,e,"ym")&" months, "&DATEDIF(s,e,"md")&" days")
( Not working )

with regards


A.Senthil Murugan

You need a cell with this formula in it and link the cell to the textbox. When either of the dates change then the value displayed in the textbox will change.

BUT - Do you want a single textbox value to be as such '39 years, 4 months, 5 days' or this split between three text boxes.

This formula checks for errors.

=IFERROR(LET(s,$A$1,e,$B$1,DATEDIF(s,e,"y")&" years, "&DATEDIF(s,e,"ym")&" months, "&DATEDIF(s,e,"md")&" days"),"")

 

[Alex Blakenburg]

I am a bit confused.
a) Is TextBox1 / Cell A2 the End Date or the start date ?
b) You are populating ss & SS1 before you are populating the text boxes using A2 and B2.
Is it the intention to use the values from A2 and B2 ?

@Joe4 VBA DateDiff does not work the same as Excel DateDif ie:

? DateDiff("m", "05/08/1985", "10/12/2024")
produces:
 472

I am thinking you will need to use something like:
(originally posted by Chip Pearson but there are a few incorrect variations of this out there)
I am login off for the night but we can adapt this to fit your requirement once you clarify whether the intention is to use the values in A2 & B2 or not and which of is the start and which is the end value. Unless @HighAndWilder can give you a solution after you answer his question.

Function Age(Date1 As Date, Date2 As Date) As String
     Dim Y As Integer
     Dim M As Integer
     Dim D As Integer
     Dim Temp1 As Date
     Temp1 = DateSerial(Year(Date2), Month(Date1), Day(Date1))
     Y = Year(Date2) - Year(Date1) + (Temp1 > Date2)
     M = Month(Date2) - Month(Date1) - (12 * (Temp1 > Date2))
     D = Day(Date2) - Day(Date1)
     If D < 0 Then
         M = M - 1
         D = Day(DateSerial(Year(Date2), Month(Date2), 0)) + D
     End If
     Age = Y & " years " & M & " months " & D & " days"
 End Function

 

[Joe4]

@Joe4 VBA DateDiff does not work the same as Excel DateDif ie:
? DateDiff("m", "05/08/1985", "10/12/2024") produces: 472

I cannot reproduce that result. It seems to work for me.
It returns 473 for me, as expected, just like the Excel "DateDif" function.

Sub MyTest()
    MsgBox "The answer is: " & DateDiff("m", "05/08/1985", "10/12/2024")
End Sub

Result:

And:

 

[Joe4]

If the date really meant to be August 5, 1985 and December 10, 2024 and not May 5, 1985 and October 12, 2024 (the old English-American date entry differences), then they both return 472 for me:

Sub MyTest()
    MsgBox "The answer is: " & DateDiff("m", "08/05/1985", "12/10/2024")
End Sub

and

EDIT: I see now from the original post, the date is meant to be today (December 10). So I am guessing it is another one of those things where they are using non-American date format, but VBA uses American date formats. It must be a pain-in-the-neck for you guys, that VBA uses the American date format, and you have to jump through these hoops!