Convert Days to Years/Months

[cglinn]

I have a query that returns a list of 2 dates, like so:

Date1 Date2
11/01/1999 04/14/2004
01/01/2003 09/23/2004
.......... ...........

What I need to do is get an average time elapsed between the two dates for the entire recordset.

So far, I loop through each row and get the difference (in days) between the two dates using the datediff() function. I add the difference to a running total to get a total number of days and then divide by the number of records to get an average number of days.

My problem is that I need to turn the average number of days into Years, Months, Days.

Any Suggestions?

 

[ClulessChris]

How about something along these lines?

lNoOfDays = DaysReturned

iNoOfYears = Cint(lNoOfDays /352)

INoOfMonths = Cint(lNoOfDays / 30)

Everybody body is somebodys Nutter.

 

[tsuji]

Hello cglinn,

dtDate1=#11/01/1999#
dtDate2=#04/14/2004#
dtDeltaD=datediff("d",dtDate1,dtDate2)
dtDeltaM=datediff("m",dtDate1,dtDate2)
dtDeltaY=datediff("yyyy",dtDate1,dtDate2)
wscript.echo dtDeltaD & vbcrlf &  dtDeltaM & vbcrlf & dtDeltaY

Summation over Loop and take average. There is highly conventional notion of year and month. In financial market you might be asked to use year=360 days... So if every expression of the average comes from days, you have to ask for the definition.

regards - tsuji

 

[cglinn]

tsuji,

Although this isn't really the solution I had originally wanted, it is also the one I had come up with. What I was really hoping for was a combination of the three for a single value. For example,

3 Years, 2 Months, and 14 Days.

Thank you all for your suggestions.

 

[tsuji]

Hello again,

For 30-day month, 360-day year, you can use something like this to do it.

df=datediff("d",dtDate1,dtDate2)
df_inwords=cstr(df\360) & " year(s) " & cstr((df mod 360)\30) & " month(s) " & cstr(df mod 30) & " day(s)"
wscript.echo df_inwords

regards - tsuji