Forum Moderators: open
Dim date1 As Date
Dim date2 As Date
Dim NoOfYears As Integer
Dim NoOfMonths As Integer
Dim NoOfDays As Integer
date1 = Now
date2 = "03/07/58"
[edit took out line below]
'NoOfYears = DateDiff("yyyy", date2, date1)
If Year(date1) >= Year(date2) Then
NoOfYears = Year(date1) - Year(date2)
Else
NoOfYears = Year(date2) - Year(date1)
End If
If Month(date1) >= Month(date2) Then
NoOfMonths = Month(date1) - Month(date2)
Else
NoOfMonths = Month(date2) - Month(date1)
End If
If Day(date1) >= Day(date2) Then
NoOfDays = Day(date1) - Day(date2)
Else
NoOfDays = Day(date2) - Day(date1)
End If
Notice how it cannot fail no matter what order the dates are put in.
You could make it more efficient though my taking the month(datex), year(datex), etc. and assigning them to variables so that the functions are only called once. The condition, (if then), statements will make no function calls instead of 4 each. This takes less stack space just for openers. Of course I come from the days when 8k of RAM was enough to write a program if one was REAL efficient. But my customers DO say my software runs much faster than all my competitors software. But I have done stuff like than in every procedure, function, etc. It IS the total of the sum.
Speaking of efficient code, I did make one modification to your code that I didn't mention out of concern for possibly offending you. I am fussy enough about my code to know the date1 parameter will always be greater than the date2 parameter so I removed your code that checks for and corrects this problem. As a result there's no need to use variables as you suggested.
Thanks again for your help. I hope I'll eventually get to return the favor.
Option Explicit
Private Function ffGetDateDiff(StartDate As Date, EndDate As Date) As String
Dim liDiffYears As Integer
Dim liDiffMonths As Integer
Dim liDiffDays As Integer
liDiffYears = DateDiff("yyyy", StartDate, EndDate)
liDiffMonths = DateDiff("m", StartDate, EndDate)
liDiffDays = DateDiff("d", StartDate, EndDate)
If liDiffMonths > 12 Then liDiffMonths = liDiffMonths - liDiffYears * 12
If liDiffDays > 365 Then liDiffDays = liDiffDays - liDiffYears * 365
If liDiffDays > 30 Then liDiffDays = liDiffDays - liDiffMonths * 30
ffGetDateDiff = liDiffYears & " year(s) " & liDiffMonths & " month(s) " & liDiffDays & " day(s)"
End Function
Private Sub Command1_Click()
MsgBox ffGetDateDiff("31.12.2002", "01.01.2003")
End Sub
If Date 1 is Dec 31 2002 and Date 2 is Jan 1 2003, you'll get (from your code) "1 year 11 months 30 days" or from my DateDiff()-based function "1 year(s) 1 month(s) 1 days(s)" where the real difference is only one day
One question thougt: If you want the absolute difference, why don't you use
NoOfYears = Abs(Year(Date1) - Year(Date2))
I didn't bother testing it. Now we all know why WE MUST ALWAYS TEST CODE BEFORE GOING LIVE WITH IT. Even a little routine can bite ya! Can't tell you how manytimes myself as well as others do that. Just chuck some code into a routine without testing it. Of course since I'm not getting paid for this, he got a lot more than he paid for.
I'm not going to mess with it any more, but I'm sure if I did the bug would be fixed. I didn't use the datediff so that it would be more portable. Comes from 25 years of writing in many different languages. Or of course having Microsoft just decide to take a function out. I just sat down and entered the code into the message so I didn't actually test it.
>One question thougt: If you want the absolute difference, why don't you use
Actually I do that in my software. Have you ever had a formal programming class, like in collage? They never jump to the shorter way of doing things because they want you to see how it works. Which is also why I didn't put the day, month and year function into a var. the 1st time.
Of course, you're right on all points mentioned. The code I posted doesn't work any better (the reason why I didn't post in in the first place), and it's as much as I got with some testing - realising that with months having changing number of days and even leap years aren't 100% regular, it qould take quite some time to write an exact calculation-routine, time I unfortunately don't have.
Youre code being portable is nice, but GaryK asked specifically for VB, so I didn't bother thinking about portability - same goes for abs(). It's nice to write code by the book, I learned that too some time ago, but I believe that on a larger scale it will be less effitient.
>the reason why I didn't post in in the first place
Yea, well believe me, you are smarter than me, I should not have either ;-))
I did find something @ Microsoft. If you goto support.microsoft.com and do a search for QRYSMP97.exe you will find, I think it was the 1st item in the list, a compressed file of an Access97 database that has 2 VBA functions in it. One called AgeYear and AgeMonth, but there is not an AgeDay. I have no clue if it works or not.
And lets not for get the international problems that crop up with this kind of stuff as well. Lucky for me, I do SPC software where dates are just markers in time that do not need to be manipulated. I hate doing accounting software.
Cheers
Here's my function for VB6 as it stands now. Feel free to laugh. I think it deals with mis-matched parameters and the year-end issue. Is there anything I'm missing?
Public Function DateDiffToYMDString(ByVal paramDate1 As Date, ByVal paramDate2 As Date) As String
Dim NoOfYears As Integer
Dim NoOfMonths As Integer
Dim NoOfDays As Integer
Dim Date1 As Date
Dim Date1_Year As Integer
Dim Date1_Month As Integer
Dim Date1_Day As Integer
Dim Date2 As Date
Dim Date2_Year As Integer
Dim Date2_Month As Integer
Dim Date2_Day As Integer
Dim FormatString As String
If paramDate2 > paramDate1 Then
Date1 = paramDate1
Date2 = paramDate2
Else
Date1 = paramDate2
Date2 = paramDate1
End If
Date1_Year = Year(Date1)
Date1_Month = Month(Date1)
Date1_Day = Day(Date1)
Date2_Year = Year(Date2)
Date2_Month = Month(Date2)
Date2_Day = Day(Date2)
If (Date1_Month = 12) And (Date1_Day = 31) And (Date2_Month = 1) And (Date2_Day = 1) Then
NoOfYears = Date2_Year - Date1_Year - 1
NoOfMonths = 0
NoOfDays = 1
Else
NoOfYears = Date2_Year - Date1_Year
NoOfMonths = Date2_Month - Date1_Month
NoOfDays = Date2_Day - Date1_Day
End If
If NoOfYears = 1 Then
FormatString = "1 year, "
Else
FormatString = CStr(NoOfYears) & " years, "
End If
If NoOfMonths = 1 Then
FormatString = FormatString & "1 month, "
Else
FormatString = FormatString & CStr(NoOfMonths) & " months, "
End If
If NoOfDays = 1 Then
FormatString = FormatString & "1 day"
Else
FormatString = FormatString & CStr(NoOfDays) & " days"
End If
DateDiffToYMDString = FormatString
End Function
Did you look at the MS stuff?
There's still one problem, which won't be as easy to solve: Try your function with the dates
- "Feb 28 2003" and "Mar 1 2003" = 1 month, -27 days
or
- "Apr 30 2003" and "May 1 2003" = 1 month, -29 days
Not quite as easy, especially if you want a correct result all the time and therefore have to calculate leap years (and exceptions to it, like the year 2100) - probably the reason why M$ only published samples for years and months.
