Datediff not showing Years, Months

[Iain Anderson]

Hi guys,

I would greatly appreciate some help with my problem.
I'm using the following formula, but it only gives me an error message.

=DateDiff("y",[joined],[DateOfResignation] & " yrs, " & DateDiff("m",[joined],[DateOfResignation] & " mths"))

I'm trying to show the result as Years, Months.
Is it possible using just a formula rather than VBA code.

Thanks in advance,
Iain Anderson

 

[boblarson]

The correct syntax for what you have is:

=DateDiff("yyyy",[joined],[DateOfResignation]) & " yrs, " & DateDiff("m",[joined],[DateOfResignation]) & " mths"

But you should know that datediff isn't necessarily going to give you what you want. If someone joined in December of 2006 and resigns in January 2007 DateDiff("y",Date1,Date2) will tell you 1.

 

[Iain Anderson]

Hi Bob,

I'm still getting an error message; Name?
Any suggestions?

Thanks, Iain

 

[boblarson]

If this is on a form, then you need to make sure that the controls are not named the same as the fields and then you need to refer to the Fields (same exact syntax as listed before, but when you change the name of the text box to txtJoined or txtDateOfResignation, it will change the code to those.

 

[pbaldy]

In addition to Bob's comments, there's also a flaw in the logic of your formula. The months portion will return the count of all months between, not just the remainder over the number of years (which I assume is what you want). IOW, you'll get "2 years 26 months" instead of "2 years 2 months". Here's something I just tested, you can replace with your fields:

Int(DateDiff("m",[ReqDateTime],Date())/12) & " years " & DateDiff("m",[ReqDateTime],Date()) Mod 12 & " months"

There may be a better way.

 

[raskew]

Hi -

Try this:

Function fAgeYM(StartDate As Date, EndDate As Date) As String
'Purpose:   Returns the difference between StartDate and EndDate in full years and months
'Coded by:  raskew
'To call:
' ? fAgeYM(#1/21/04#, #1/19/06#)
'Returns:
' 1 year 11 months

Dim intHold As Integer

   intHold = Int(DateDiff("m", StartDate, EndDate)) + _
             (EndDate < DateSerial(year(EndDate), month(EndDate), Day(StartDate)))
   
   fAgeYM = Int(intHold / 12) & " year" & IIf(Int(intHold / 12) <> 1, "s ", " ") & intHold Mod 12 & " month" & IIf(intHold Mod 12 <> 1, "s", "")

End Function

If you wanted to return years, months, days, you might give this a shot:

Function fAge(dteStart As Variant, dteEnd As Variant) As Variant
'*******************************************
'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

Bob

 

[Iain Anderson]

Datediff not showing years and months

Hi guys,

Many thanks for your suggestions. However, none of them does the trick
Each time I end up with #NAME? despite creating new fields in order to call the 2 calculated fields and reference them that way.

Cheers,
Iain

 

[boblarson]

It doesn't happen to be on a subform by any chance does it?

 

[Iain Anderson]

Datediff not showing years and months

Hi Bob,

Excuse the delay in replying.
No, it is not on a subform, it is on the main form.

Cheers,
Iain

 

[tt4t Nick]

OK, let's rewind a little here! Most folks who look here are after SIMPLE solutions to their problems. Usually without using VBA code or anything like it. Code is great if you are used to using it, however, most of us don't.

What they want is the formula needed to directly enter into a control in a field or, (preferably), the formula for insertion in a calculated field in the Query Design Grid (QDG).

The age thing is truly simple if you keep the approach segmented and simple e.g. (for a person with a dob of 22/03/1952 as at today's date 08/11/2010)

In the QDG create a calculated field so (there must be an existing field for the date of birth named [dob]):

Age2: DateDiff("yyyy",[dob],Now())+Int(Format(Now(),"mmdd")<Format([dob],"mmdd"))

This will produce the age of the individual in YEARS (58)

In the QDG create another calculated field so:

Age3: DateDiff("m",[dob],Now())+Int(Format(Now(),"mmdd")<Format([dob],"mmdd"))

This will produce the age of the individual in MONTHS (704)

Final calculated field to create is:

Age4: ([age3]-([age2]*12))

This produces a field with the result of the calculation [age3] (704) - [age2]*12 i.e. 58x12= (696)

704-696= 8

The two age fields therefore read 58 years and 8 months. You could use the same process to add days into the calculation if required.

The last thing to do is create a calculated field on your form or report which has the following formula:

=IIf([age2]>1,[Age2] & "yrs " & [age4] & "mths",[age2] & "yr " & [age4] & " mths")

This will put the result together in one field and "take care" of anyone not yet two years old who requires year instead of years after the year age.

 

[Dairy Farmer]

Years
 IIf([DOB] Is Null,"",DateDiff("yyyy",[DOB],Date())-IIf(Format([DOB],"mmdd")>Format(Date(),"mmdd"),1,0))

Months
IIf([DOB] Is Null,"",IIf(Day([DOB])<=Day(Date()),DateDiff("m",[DOB],Date())-[Years]*12,DateDiff("m",[DOB],Date())-[Years]*12-1))

Days
IIf([DOB] Is Null,"",DateDiff("d",DateAdd("m",[Months],DateAdd("yyyy",[Years],[DOB])),Date()))