0 votes
in VBA by Beginner (71 points)


i hope somebody help  to find macro or udf  indeed i search so much in the internet  to calculate accuratly  difference between two date   the function datediff doesn't give  the accurate result   so i need macro or udf  to  gives me   right values years,months days   what  i  got   so  far   from the internet  it works for month   i would  apprecite  if anybodyhelp  also  make  it   year and  day  

as you see in my  image  

for instance  1/7/2020 and 30/6/2020     the  right result is 

year=1  and  month=12 and  days  = 365  


Function date_diff_to_months(Date1 As Date, Date2 As Date)

   Dim y1 As Integer
   Dim y2 As Integer
   Dim d1 As Integer
   Dim d2 As Integer
   Dim m1 As Integer
   Dim m2 As Integer
   Dim m_diff As Integer
   Dim y_diff As Integer
   Dim month_adjustment As Integer

   y1 = Year(Date1)
   y2 = Year(Date2)
   m1 = Month(Date1)
   m2 = Month(Date2)
   d1 = Day(Date1)
   d2 = Day(Date2)

   m_diff = m2 - m1
   y_diff = (y2 - y1) * 12

   If (m_diff > 0 Or y_diff > 0) Then
       If (d1 <= 15 And d2 >= 15) Then
          month_adjustment = 1
       ElseIf (d1 >= 15 And d2 <= 15) Then
          month_adjustment = -1
       End If
   End If

   date_diff_to_months = m_diff + y_diff + month_adjustment
End Function

thanks advance

Please log in or register to answer this question.

Welcome to wellsr Q&A
Ask any questions you have about VBA and Python and our community will help answer them. wellsr Q&A is the standalone question and answer platform for wellsr.com. If you have a question about one of our specific tutorials, please include a link back to the tutorial.

Getting Started
VBA Cheat Sheets (On Sale Now)

Looking for something else? Hire our team directly through ourVBA Help page, instead.

For more programming tips visit the VBA Tutorials Blog and the Python Tutorials Blog.