Calculate the number of Month based on the Dates Entered in Textboxes

Question

I have a userform in which three textboxes are there. Textbox1 belongs to Start date Textbox2 belongs to End Date Textbox3 belongs to No of Months.

Below is my code.

Dim mon As Integer

'Check Whether the Start Date Text Box is Empty or Not
If IsNull(Me.TextBox1.Text) = True Then
Me.TextBox3.Value = ""

Else
Me.TextBox2.Value = Format(DateAdd("yyyy", 1, Me.TextBox1.Value) - 1, "dd-mmm-yyyy")

mon = DateDiff("m", Me.TextBox1, Me.TextBox2)
Me.TextBox3.Value = mon

End If

The above code calculates the end date and no. of months. The problem is when i enter the 1st date of any month, it shows me only 11 months. However, if i enter any other date except 1st, it shows 12 months.

Please advise me how can i get 12 months if i enter the 1st date of any month.

Thanks Salman

Why you minus 1 day in TextBox2? 01-Oct - 1 will be 30-Sep, and there are only 11 months from Oct-2018 to Sep-2019. If you do need the - 1, then add it back in DateDiff. — newacc2240
I did minus one so that end date will come one day back as of start date. i.e if i have entered start date as 01-09-2018 then end date should come 31-08-2018. For this reason, i did minus 1. However if i add 1 again in datediff, it exceeds the number of months to 13. — Salman Khan
Well, I don't get a 13 for my testing. However since the input of TextBox2 is not from the user, why don't you just assign 12 to TextBox3 :P — newacc2240

Shelty Shelty · Accepted Answer · 2018-09-29T21:01:25

Well, I tested this code and it works nice:

Sub Test()

Dim mon As Integer

'Check Whether the Start Date Text Box is Empty or Not
If IsNull(Cells(4, 3).Text) = True Then
Cells(5, 3).Value = ""

Else
Cells(4, 4).Value = DateAdd("yyyy", 1, Cells(4, 3).Value - 1)
Cells(4, 4).NumberFormat = "d-mmm-yyyy"

mon = DateDiff("m", Cells(4, 3), Cells(4, 4) + 1)
Cells(5, 3).Value = mon

End If


End Sub

When Format function was used, I couldn't just add 1 day in DateDiff. It is because you can't add 1 to a string, I guess.

Sub Test2()
Dim xDate As Long
Dim xNewDate As Long

xDate = 40179  'It is 01.01.2010
xNewDate = DateAdd("yyyy", 1, xDate - 1)

xDateFormat = Format(xDate, "dd-mmm-yyyy")
xNewDateFormat = Format(xNewDate, "dd-mmm-yyyy")


MsgBox DateDiff("m", xDate, xNewDate + 1)

Debug.Print xDate
Debug.Print Format(xDate, "dd-mmm-yyyy")
Debug.Print xNewDate
Debug.Print Format(xNewDate, "dd-mmm-yyyy")

Debug.Print ""

'Debug.Print CLng(xDateFormat)                                                                 'Type Mismatch
'Debug.Print CLng(xNewDateFormat)                                                              'Type Mismatch
Debug.Print DateDiff("m", xDateFormat, xNewDateFormat)                                         'It works so DateDiff converts it
Debug.Print DateDiff("m", DateDiff("d", 0, xDateFormat), DateDiff("d", 0, xNewDateFormat) + 1) 'Now you can add one day


End Sub

I hope that this code should work:

Dim mon As Integer

'Check Whether the Start Date Text Box is Empty or Not
If IsNull(Me.TextBox1.Text) = True Then
Me.TextBox3.Value = ""

Else
Me.TextBox2.Value = Format(DateAdd("yyyy", 1, Me.TextBox1.Value) - 1, "dd-mmm-yyyy")

mon = DateDiff("m", Me.TextBox1, Me.TextBox2)
mon = DateDiff("m", DateDiff("d", 0, Me.TextBox1), DateDiff("d", 0, Me.TextBox2) + 1)
Me.TextBox3.Value = mon

End If

Calculate the number of Month based on the Dates Entered in Textboxes

1 Answers