0

I need to find the difference between 2 dates in months and days in VB. Using DateDiff() function, I am not getting the desired result since DateDiff() subtracts the corresponding values only. For example:

DateDiff("m", '31/01/2012', '1/02/2012')

Returns 1 month, which is incorrect. How do I achieve this?

JasonMArcher
  • 14,195
  • 22
  • 56
  • 52
Ritwik Dey
  • 559
  • 1
  • 7
  • 17
  • What will be the desired result? Do you want the result in days count only or? – Alex Jun 17 '14 at 13:46
  • 1 Day, in case of the above example or 0 months. I want the result in the form of months and dats. For Eg: if start date is 31/01/2012 and end date is 5/3/2012, I need to have 1 month and 5 days as answer. – Ritwik Dey Jun 17 '14 at 13:47
  • try to use "d" instead of "m" to get the number of days difference – Bjorn Jun 17 '14 at 13:50
  • That gives me the number of days. I need to have it in days and months. – Ritwik Dey Jun 17 '14 at 13:54
  • 2
    For this purpose, how many days should your "month" contain? 30, 31, 28, 29? – GSerg Jun 17 '14 at 13:58
  • 2
    You're going to need to create a custom function to return that type of information, by converting the number of *days* to a month part and a day part. Your custom function will need to account for the number of days in each month (e.g., February 1 to April 3 is 63 days, and would result in 2 month, 3 days -- whereas April 1 to June 4 is 64 days but would also be 2 month, 3 days). You will also need to account for leap years... – David Zemens Jun 17 '14 at 13:59
  • What is the purpose of DateDiff() then? A simple class 2 Subtraction is what it does? We could have achieved the same result by seperating the different parts of the date and subtracting them. – Ritwik Dey Jun 17 '14 at 15:02
  • 1
    @RitwikDey Yes you could, but it would be more difficult (again). E.g. `Month(d1) - Month(d2)` only makes sense if `d1` and `d2` belong to the same year. `DateDiff` handles all that. Also see [this discussion](http://stackoverflow.com/q/1083955/11683). – GSerg Jun 17 '14 at 15:07
  • Do we have a monthend sort of function in VB, like we have one in PLSQL. That would make things easier. – Ritwik Dey Jun 17 '14 at 15:55
  • @RitwikDey No we don't. [We do in Excel VBA](http://office.microsoft.com/en-us/excel-help/eomonth-HP005209076.aspx) provided the Analysis Toolpack is installed. Outside of Excel, you can write one yourself: `result = DateAdd("m", 1, DateSerial(Year(d), Month(d), 1)) - 1`. – GSerg Jun 17 '14 at 16:41

2 Answers2

1

I have solved this. I had to write a function that does what I need. The algorithm is :

1) Use DateDiff to calculate the difference in months between 2 days. 2) Store the 'dd' part of both the dates in 2 different variables. 3) If 'dd' of start date is greater than 'dd' of end date, month will be Step1.value-1, else, month will be step1.value 4) Increment the Start date by the value of months we get from step 3 5) Run a loop from 1 to 31 incrementing start date by 1 until it is equal to end date. 6) value of days will be (no. of iterations)

The code is :

Dim temp As Date
Dim temp1 As Integer
Dim i As Integer
Dim day1 As Integer
Dim day2 As Integer


 temp1 = DateDiff("m", StartDate, EndDate)

 day1 = DatePart("d", StartDate)
 day2 = DatePart("d", EndDate)
 If day1 > day2 Then
    temp = DateAdd("m", (temp1 - 1), StartDate)
    Month = (temp1 - 1)
Else
    temp = DateAdd("m", (temp1), StartDate)
    Month = (temp1)
End If

For i = 1 To 31
    If temp = EndDate Then Exit For
    temp = DateAdd("d", 1, temp)
Next i

Day = (i - 1)
Ritwik Dey
  • 559
  • 1
  • 7
  • 17
0

For Days Count Between Two Dates in VB6.0

Dim date1 As Date
Dim date2 As Date
date1 = CDate(Text1.Text)
date2 = CDate(Text2.Text)
Text3.Text = "Days between dates is: " & Format$(date2 - date1)
Jignesh Ravte
  • 66
  • 1
  • 2