# of months and years between 2 dates

[MCPFLO]

I need assistance in converting the formula from excel DateDiff (cell#1, cell#2,"Y") & "/" & DateDiff (cell#1, cell#2,"YM"). I need the result to show the number of years/months in between 2 dates. I tried to use the following formula but no luck it returns error timeframe: DateDiff ([Dateone], [DateTwo],"Y") &"/" & DateDiff ([Dateone], [DateTwo],"YM"). Thanks for the help

 

[KenHigg]

DateDiff("m",[dateone],[datetwo])


???
kh

 

[WayneRyan]

MC,

If greater than a year: year(s)/month(s)
Otherwise: just number of months.

IIf(DateDiff("y", [Dateone], [DateTwo]) > 0, 
    DateDiff("y", [Dateone], [DateTwo]) & " years/" & DateDiff ("m", [Dateone], [DateTwo]) & " months",
    DateDiff("m", [Dateone], [DateTwo]) & " months")

Wayne

 

[Brianwarnock]

Do you mean complete years and months? as 01/01/2001 and 01/02/2002 is 1year/1month

The above formulae do not work in any situation as datediff just subtracts the years eg 31/12/2003 to 01/01/2004 gives 1 year/12months????
You will need to write a function unless somebody as allready done so, look at Pat Hartman's sample database there may be one there.

Brian

 

[WayneRyan]

Good catch Brian,

I'm still not giving this much thought, but this might work. Added the
Mod function:

IIf(DateDiff("y", [Dateone], [DateTwo]) > 0, 
    DateDiff("y", [Dateone], [DateTwo]) & " years/" & DateDiff("m", [Dateone], [DateTwo]) Mod 12 & " months",
    DateDiff("m", [Dateone], [DateTwo]) & " months")

Wayne

 

[MCPFLO]

Do you mean complete years and months? as 01/01/2001 and 01/01/2002 is 1 year/0 month........ Yes something like this or another example 01/01/2001 and 02/15/2001 is 0 year / 1 month. All I am trying to display is the number of years and months difference even if it is zero. Thanks for the help.... so far it I have the formula in excel but access will show all my people together and I can create a form to display how long they have been since their last promotion

 

[Rose412]

Jon K posted a function in this thread to display time elapsed in ? years ? months ? days (you can easily remove the days if you want.)

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

Rose