Happy New Year & Happy Sunday!
I need help in calculating the difference between 2 dates (the result should be like "1 year, 2 months and 15 days"). I am building an application for my team to show their tenure in the organization (LIVE TENURE FROM THE DATE OF HIRE TILL NOW). I have the formula in EXCEL but I am not able to get it converted into Visual Basic (I am only a beginner).
[color=Red][size=4][b]I am using Visual Basic 6.0 along with ADO connection.[/b][/size][/color]
The formula in Excel is "=YEAR(TODAY())-YEAR(E337)-IF(OR(MONTH(TODAY())=DAY(E337)),12,IF(AND(MONTH(T ODAY())>MONTH(E337),DAY(TODAY())<DAY(E337)),-1)))&" months, "&TODAY()-DATE(YEAR(TODAY()),MONTH(TODAY())-IF(DAY(TODAY())<DAY(E337),1,0),DAY(E337))&" days""
I need this to be completed very soon and I appreciate any help that I can get on this question.
Looking forward to hear from you,
VB6 has the DateDiff function. (look it up in your MSDN Library for more information).
The following will demonstrate its function a bit;
strDate1 = "01-01-2000" 'date of hire
strDate2 = Format(Date, "DD-MM-YYYY")'now
If IsDate(strDate1) = False Or IsDate(strDate2) = False Then
MsgBox "Invalid Date"
MsgBox DateDiff("yyyy", strDate1, strDate2, vbMonday, vbFirstJan1) & " Years"
MsgBox DateDiff("m", strDate1, strDate2, vbMonday, vbFirstJan1) & " Months"
MsgBox DateDiff("d", strDate1, strDate2, vbMonday, vbFirstJan1) & " Days"
Downside of the function is it will return the amount of years passed correctly, but also all the months and all the days that have passed between the two dates. You can't directly get the result you're asking.
Keep some things in mind;
-- Regional settings of the date of the operating system; '01-03-2007' could mean the 3rd of January or the 1st of March, depending on the regional settings.
-- You'll get undesirable results when strDate2 is before strDate1
-- You'll need to create a method to best calculate the results you want to present. And when calculating, keep track of the leap years (1 extra day per 4 years) :P
Hope this will get you on the right track.
[code]If ... Then