Member Avatar for victor77

Greetings!!!

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).

I am using Visual Basic 6.0 along with ADO connection.

The formula in Excel is "=YEAR(TODAY())-YEAR(E337)-IF(OR(MONTH(TODAY())<MONTH(E337),AND(MONTH(TODAY())=MONTH(E337),DAY(TODAY())<DAY(E337))),1,0)&" years, "&MONTH(TODAY())-MONTH(E337)+IF(AND(MONTH(TODAY())<=MONTH(E337),DAY(TODAY())<DAY(E337)),11,IF(AND(MONTH(TODAY())<MONTH(E337),DAY(TODAY())>=DAY(E337)),12,IF(AND(MONTH(TODAY())>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,

Thank You,
Venugopal

  • 4 Contributors
  • 5 Replies
  • 149 Views
  • 1 Day Discussion Span
  • Latest Post Latest Post by SCBWV
Member Avatar for jbennet

I know in VBA you can use DateDiff (i use it in MS access to work out the number of days simce last backup)

Google datediff.

here is how i use it

'if the current date is more than 7 days after last backup make it display a messagebox, asking them if they have backed up with options for YES or NO -if yes then change last backup to the current date

Dim difference As Integer
Dim result As Integer

'use the datediff funtion to work out the difference between two dates in days

difference = DateDiff("d", Text25, Text28)

'd means count in days - text 28 is current date, 25 is date last backedup

If difference >= 7 Then
result = MsgBox("You have not backed up this week! Please Back Up Now!", vbYesNo, "Backup Prompt")
If result = 6 Then
Text25 = Now()
Else
MsgBox ("Please backup at the next opportunity!")
End If
End If
Member Avatar for victor77

Hi,
Thanks for the reply. DateDiff works but when I try I am getting the following result for these two dates -

Example: I need the difference between #01/12/2004# and today (#01/13/2008#). It should be "4 years, 0 months and 1 days(s)".

I am getting "4 years, 48 months and 1462 days" which is not correct. I need to know the correct method to get the above mentioned structure - "4 years, 0 months and 1 days(s)".

Hope I didnt confuse you.

Thank You,

Member Avatar for jbennet

you using excel 2007?

office 2007 has a bug in the date system- a hottfix is available

Member Avatar for SCBWV

Maybe look at the Mod function... interesting, 1462 Mod 365 = 2. Wonder when DateDiff starts?

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.