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]

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

Hi Sir,

Thank for your Function "Age". It is working fine & result as expected in cell .

How to use this formula in Textboxes.

if the Date1, Date2 is in Textboxes 1 & 2 and result in Textbox 3

with regards


A.Senthil Murugan

 

[Alex Blakenburg]

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

Except that the requirement is days month years shown in original post as md, ym, y.
Also VBA Datediff counts each change of year /month while Excel datdif counts completed year/month

 

[Joe4]

Except that the requirement is days month years shown in original post as md, ym, y.
Also VBA Datediff counts each change of year /month while Excel datdif counts completed year/month

As I mentioned in my last post, I am using the American version of Excel, which makes it a bit harder to recreate and test the scenarios that you guys have to deal with.
So I will leave this to you guys, as you seem to have it under control!

 

[Alex Blakenburg]

You didn't answer the question on which is the start and which is the end date so you are you going to have to swap that around if I have it back to front.
I have
A2>TextBox1 = Start
B2>TextBox2 = End.

To use the Function I gave you previously,

Option 1 - Text Sentence (years, months, days) in TextBox3

Private Sub CommandButton1_Click()
    Dim StartDate As Date, EndDate As Date

    TextBox1.Value = Format(Range("A2").Value, "dd-mm-yyyy")
    TextBox2.Value = Format(Range("B2").Value, "dd-mm-yyyy")

    StartDate = CDate(TextBox1.Value)
    EndDate = CDate(TextBox2.Value)

    TextBox3.Value = Age(StartDate, EndDate)

End Sub

Option 2 - days, months, years TextBox 3,4,5 respectively

Private Sub CommandButton1_Click()
    Dim StartDate As Date, EndDate As Date
    Dim dd_Split As Variant
    Dim dd_y As Long, dd_m As Long, dd_d As Long

    TextBox1.Value = Format(Range("A2").Value, "dd-mm-yyyy")
    TextBox2.Value = Format(Range("b2").Value, "dd-mm-yyyy")

    StartDate = CDate(TextBox1.Value)
    EndDate = CDate(TextBox2.Value)

    dd_Split = Split(Age(StartDate, EndDate), " ")

    dd_y = dd_Split(0)
    dd_m = dd_Split(2)
    dd_d = dd_Split(4)
    TextBox3.Value = dd_d
    TextBox4.Value = dd_m
    TextBox5.Value = dd_y
End Sub