count date

[Ms.Sigma]

hello all,

I know how to count date in Excel worksheet, but I met problem if I do it in userform.

My way for counting date in worksheet is below:

A1=date(2011,1,1) (the output is 2011-1-1)
A2=date(2011,2,1) (the output is 2011-2-1)

A4=(A2-A1)/365 (the output is 0.085)

Now, I want to show above information in a userform. I made an userform, including three textbox and one commandbutton. The textbox1 and textbox2 are for inputing the start date and the end date, respectively. The textbox3 will show the expected value if I click commandbutton. And, the expected value is computed by (textbox2.value-textbox1.value)/365.

Here is the macro of VB:

Function countdate(a, b)

countdate = (b - a) / 365

End Function




Private Sub CommandButton1_Click()

Dim a, b As Date

a = TextBox1.Value
b = TextBox2.Value

TextBox3.Value = countdate(a, b)

End Sub

Could anyone indicate my mistake of macro? Many thanks!!

 

[Ms.Sigma]

sorry, the macro for commandbutton click event is:

Private Sub CommandButton1_Click()

Dim a, b As Variant

TextBox1.Value = Format(Date, "yyyymmdd")
TextBox2.Value = Format(Date, "yyyymmdd")

a = TextBox1.Value
b = TextBox2.Value

TextBox3.Value = countdate(a, b)

End Sub


The problem is: when I click commandbutton, the values in textbox1 and textbox2 will automatically change to the date of today, and the value of textbox3 of course becomes zero. What is my mistake?

 

[Ms.Sigma]

bump

 

[Ms.Sigma]

anyone can help me?

 

[Ms.Sigma]

 

[Marcelo Branco]

Hi,

Maybe this

Private Sub CommandButton1_Click()
    Dim a As Date, b As Date
    a = Format(TextBox1, "dd/mm/yyyy")
    b = Format(TextBox2, "dd/mm/yyyy")
    TextBox3.Value = Format(countdate(a, b), "#,##0.0000")
End Sub

My regional setting for dates is dd/mm/yyyy
I entered this format in the text-boxes and it seems to work

HTH

M.