[AccessD] Dividing days into years and months

Many thanks, Stuart, that looks excellent. 

-----Original Message-----
From: Stuart McLachlan [mailto:stuart at lexacorp.com.pg]
Sent: Wednesday, July 28, 2004 12:54 PM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] Dividing days into years and months


On 28 Jul 2004 at 8:52, Paul Rodgers wrote:

> Dear Experts
> 
> I can divide in Access the days from a date() into years with a simple
> /365,

No you can't because Leap Years don't have 365 days :-(

> but is there a way to have months and days ... so I have years, months
> and days, please? 
> 

First, you need to determine what answers you want.
Take  28 Feb 03 to 1 Apr 03 as an example:
Is that 1 month and 1 day (end of Feb to end of Mar + 1 day)
or 1 month and 4 days (28 Feb to 28 Mar + 4 days)

You will need to develop functions based on which interpretation you want.

Here's one I've just knocked up that will return the second answer. I'll
leave 
it to you to work out a method if you want the first answer.
BTW, I've just copyrighted the word monthiversary :-)

Function YMDDiff(Date1 As Date, Date2 As Date) As String
Dim years As Long
Dim months As Long
Dim days As Long
Dim monthiversary As Date

months = DateDiff("m", Date1, Date2)
years = Int(months / 12)
monthiversary = DateAdd("m", months, Date1)
If Date2 - monthiversary < 0 Then
	months = months - 1
	days = Date2 - DateAdd("m", -1, monthiversary)
Else
	days = Date2 - monthiversary
End If
months = months - (years * 12)
YMDDiff = years & " years, " & months & " months" & " and " & days & " days"
End Function




 
-- 
Lexacorp Ltd
http://www.lexacorp.com.pg
Information Technology Consultancy, Software Development,System Support.



-- 
_______________________________________________
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com