Difference Between Two Dates Into Years Months Days (1 Viewer)

[iandanescourt]

Hello to everybody.

I have a problem in calculating the difference between two dates and displaying the output into Years Months Weeks Days. My problem is that dividing the number of days by 30 and requesting an integer is not accurate enough. I have looked at Pat and Jons examples shown in these threads

http://www.access-programmers.co.uk/forums/showthread.php?t=67472

http://www.access-programmers.co.uk/forums/showthread.php?t=89051&highlight=calculating+number+months

which either method would suit me as the accurate answer is what I am after.

My problem is that not all of my records would have a start and end date. Therefore both examples would result in an error.

Can anyone please give a hint on how to perform the calculations on those fields which have dates recording the results but ignoring those fileds which do not have a start / end date.

I am having a bad day and I'm sure the answer is so easy that I am going to kick myself but having played around with the modules by limited knowledge is not enough.

Many thanks in advance

Ian

 

[NigelBrown]

Hi Ian,

You don't say where you're performing the calculation but try the Iif statement;

Iif([Start_date]="", do the calculation,0)

 

[raskew]

Hi -

As you've noted, we can't assume that every month has 30 days, nor every year has 365 days because neither is the case. If we can determine the actual number of full months between dteStart and dteEnd then:
1) Using the Int (\) operator and dividing by 12 returns the number of years.
2) Using the mod operator, e.g. X mod 12 returns the number of months.
3) The only tricky part is the days. Try copying/pasting the following code to a new module and testing it from the debug (immediate) window as described in the comments.

This doesn't resolve the situation where either the dteStart or dteEnd is missing. Don't have a good example db to test it on but think that Nigel's solution will fill the bill.

Function fAge(dteStart As Date, dteEnd As Date) As String
'*******************************************
'Purpose:   Accurately return the difference
'           between two dates, expressed as
'           years.months.days
'Coded by:  raskew
'Inputs:    From debug (immediate) window
'           1) ? fAge(#12/1/1950#, #8/31/2006#)
'           2) ? fAge(#12/30/2005#, #1/2/2006#)
'Outputs:   1) 55.8.30
'           2) 0.0.3
'*******************************************

Dim intHold   As Integer
Dim dayHold   As Integer


   'correctly return number of whole months difference
   'the (Day(dteEnd) < Day(dteStart) is a Boolean statement
   'that returns -1 if true, 0 if false
   intHold = DateDiff("m", dteStart, dteEnd) + (Day(dteEnd) < Day(dteStart))
   
   'correctly return number of days difference
   If Day(dteEnd) < Day(dteStart) Then
      dayHold = DateDiff("d", dteStart, DateSerial(Year(dteStart), Month(dteStart) + 1, 0)) + Day(dteEnd)
   Else
      dayHold = Day(dteEnd) - Day(dteStart)
   End If
   
   fAge = LTrim(str(intHold \ 12)) & "." & LTrim(str(intHold Mod 12)) & "." & LTrim(str(dayHold))

End Function

HTH - Bob

 

[iandanescourt]

Thanks guys, when I get chance I will try that out, will get back to you to let you know how I get on

Ian