We help IT Professionals succeed at work.

DateDiff Question in Access to determine Total Months

jjc9809
jjc9809 asked
on
Hi eveyonne,

I need to determine the correct Total Months an employee has worked.  I need for it to be as accurate as possible and not round up an extra day.

I have fields;  StartDate and DateTo for 910 employees

How can I write a this in a query and get the results.

I also would like a second query developed where I can enter a date in and the quer runs subtracting the
#12/1/2011#.  The #12/1/2011#  will be the DateTo fo each of the 910 employees.

Thanks

jjc9809
Comment
Watch Question

CERTIFIED EXPERT
Top Expert 2016

Commented:
use this in a column of your query

TotalMonths:datediff("m",[StartDate],[DateTo])

just take note that

11/30/2011  as startDate  will also return 1 month for DateTo 12/1/2011

if you don't want that kind of result returned, give more explicit information or rules to follow for computing the TotalMonth

Author

Commented:
Yes, I know.  How can I avoid the extra month becase I used this same formula you have indicated and sometimes I come up with 352 when it should be 351 for example.  This is what I am trying to not do.

For xample, say a date 12/28/1985.  I get 312 when it should be 311
On 10/22/1990 to 12/1/2011 I get 254 when it should be 253

Is there a way to re-write

Author

Commented:
I had someone give me this formula:

SELECT DateDiff('m',DateAdd('m',1,[Enter Date]), #12/1/2011#) AS Total_Months from TableName
CERTIFIED EXPERT
Top Expert 2016

Commented:
< How can I avoid the extra month > define extra month
CERTIFIED EXPERT
Top Expert 2016

Commented:
try this

select  DateDiff("m", StartDate, DateTo) + (DateSerial(Year(DateTo), Month(DateTo), Day(StartDate)) > DateSerial(Year(DateTo), Month(DateTo), Day(DateTo))) as TotalMonths from tableName
CERTIFIED EXPERT
Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
Here's a function that won't fail where the simple methods do:

Public Function Months( _
  ByVal datDate1 As Date, _
  ByVal datDate2 As Date, _
  Optional ByVal booLinear As Boolean) _
  As Integer

' Returns the difference in full months between datDate1 and datDate2.
'
' Calculates correctly for:
'   negative differences
'   leap years
'   dates of 29. February
'   date/time values with embedded time values
'   negative date/time values (prior to 1899-12-29)
'
' Optionally returns negative counts rounded down to provide a
' linear sequence of month counts.
' For a given datDate1, if datDate2 is decreased stepwise one month from
' returning a positive count to returning a negative count, one or two
' occurrences of count zero will be returned.
' If booLinear is False, the sequence will be:
'   3, 2, 1, 0,  0, -1, -2
' If booLinear is True, the sequence will be:
'   3, 2, 1, 0, -1, -2, -3
'
' If booLinear is False, reversing datDate1 and datDate2 will return
' results of same absolute Value, only the sign will change.
' This behaviour mimics that of Fix().
' If booLinear is True, reversing datDate1 and datDate2 will return
' results where the negative count is offset by -1.
' This behaviour mimics that of Int().

' DateAdd() is used for check for month end of February as it correctly
' returns Feb. 28. when adding a count of months to dates of Feb. 29.
' when the resulting year is a common year.
'
' 2010-03-30. Cactus Data ApS, CPH.

  Dim intDiff   As Integer
  Dim intSign   As Integer
  Dim intMonths As Integer
  
  ' Find difference in calendar months.
  intMonths = DateDiff("m", datDate1, datDate2)
  ' For positive resp. negative intervals, check if the second date
  ' falls before, on, or after the crossing date for a 1 month period
  ' while at the same time correcting for February 29. of leap years.
  If DateDiff("d", datDate1, datDate2) > 0 Then
    intSign = Sgn(DateDiff("d", DateAdd("m", intMonths, datDate1), datDate2))
    intDiff = Abs(intSign < 0)
  Else
    intSign = Sgn(DateDiff("d", DateAdd("m", -intMonths, datDate2), datDate1))
    If intSign <> 0 Then
      ' Offset negative count of months to continuous sequence if requested.
      intDiff = Abs(booLinear)
    End If
    intDiff = intDiff - Abs(intSign < 0)
  End If
  
  ' Return count of months as count of full 1 month periods.
  Months = intMonths - intDiff
  
End Function

Open in new window


/gustav
CERTIFIED EXPERT

Commented:
'Months' is a meaningless measure - it can't be calculated meanigfully because calendar months are not uniform and it is absolutely true that comparisons of time based on 'numbers of months' are  hopelessly unreliable.

Is 31Jan - 28 feb  a month? - I guess everyone would say yes.
What about 30 Jan to 27 Feb? Or 29Jan to 26Feb? These don't sound like a month but they are the same length as 31Jan-28feb.

And is 28Feb 2003 to 28feb 2004 equal to 12 months?  
So what about  28Feb 2003 to 29feb 2004 ? is that still 12 months?

Is 28 Feb to 28 Mar a month?  What does 28 Feb to 31 Mar mean then?
CERTIFIED EXPERT
Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
A month is a month, not a count of days. If you need that, do as in the financial world where one month equals 30 days, thus one year equals 360 days.

> Is 31Jan - 28 feb  a month? - I guess everyone would say yes.
Not in leap years. That would be reached at 29. Feb.

> What about 30 Jan to 27 Feb?
Not a month. The month would be reached at 28. (or 29. in leap years) Feb.

> Or 29Jan to 26Feb?
Not a month. The month would be reached at 28. (or 29. in leap years) Feb.

> These don't sound like a month but they are the same length as 31Jan-28feb.
Yes but - as noted - a month is not a fixed count of days.

> And is 28Feb 2003 to 28feb 2004 equal to 12 months?  
Yes.

> So what about  28Feb 2003 to 29feb 2004 ? is that still 12 months?
Yes.

> Is 28 Feb to 28 Mar a month?
Yes.

>What does 28 Feb to 31 Mar mean then?
One month.

You can verify all these examples with DateAdd("m", ...)

The tricky part are those examples where you add or subtract some months to ultimo (the last day) of a month of Feb, Apr, Jun, Sep, or Nov to reach a month of Jan, Mar, May, Jul, Aug, Oct, or Dec. Some have the rule that in such cases the result should be ultimo as well. Thus, for example:

28. Feb 2001 + one month => 31. Mar
28. Feb 2004 + one month => 28. Mar
29. Feb 2004 + one month => 31. Mar

Here's a function the does this:
Public Function DateAddMonth(ByVal datDate As Date, _
                    Optional ByVal intIncrement As Integer = 1, _
                    Optional ByVal booLeapYearIgnore As Boolean = True) _
                    As Date

' Add intIncrement number of months to datDate.
' If datDate is ultimo, return date will also be ultimo.
'
' 1999-10-21, Gustav Brock, Cactus Data ApS, Copenhagen

  Dim datDateNext As Date
  Dim booUltimo As Boolean
  
  ' No specific error handling.
  On Error Resume Next
  
  ' Add one day.
  datDateNext = DateAdd("d", 1, datDate)
  ' If datDate is ultimo, next day will be the first of the next month.
  booUltimo = (Day(datDateNext) = 1)
  
  If (Not booUltimo) And booLeapYearIgnore Then
    ' Regard February 28 as ultimo also for leap years.
    If (Month(datDate) = 2) And (Day(datDate) = 28) Then
      ' Add one more day.
      datDateNext = DateAdd("d", 1, datDateNext)
      booUltimo = True
    End If
  End If
  
  If booUltimo Then
    ' Add intIncrement number of months to the first of next month.
    datDateNext = DateAdd("m", intIncrement, datDateNext)
    ' Decrement one day.
    ' As datDate is ultimo, the month will be decremented too.
    datDateNext = DateAdd("d", -1, datDateNext)
  Else
    ' Add intIncrement number of months to datDate.
    datDateNext = DateAdd("m", intIncrement, datDate)
  End If
  
  DateAddMonth = datDateNext
  
End Function

Open in new window

/gustav
CERTIFIED EXPERT
Top Expert 2016

Commented:
according to who ?
CERTIFIED EXPERT
Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
Oh my ...? Read up here, please:

Gregorian calendar

/gustav
CERTIFIED EXPERT

Commented:
gustav    -   same as cap1 - in whose opinion?  I accept that you have defined a set of rules but they are just <your> rules and might or might not have anything in common with what the poster means.

In my opinion, it's often the case that questions asking about difference in months have often not been thought through and the results (however obtained) used without any understanding of the measurement being made.
"I've just been told to get an answer in years and months"
CERTIFIED EXPERT

Commented:
Don't be patronising Gustav.  I think we all know how the calendar works.
CERTIFIED EXPERT
Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
Well, anyone can define their own rules - or lack of the same.

I just refer to:

1. Common logic and sense
2. A general rule in the financial world including all banks of the World
3. A common rule enforced in many businesses and by most accountants.

If you are not familiar with these rules, I don't blame you, but please don't blame me. They are not "mine".

The single point where many fail is to mix up a count of days with a count of months.
A month can be 28, 29, 30, or 31 days. But from those figures you cannot conclude the inverse that, say, 29 days is a month.
It's similar for years. These can be 365 or 366 days. But you cannot inverse this and say that 366 days is a year. It depends.

Weeks, however, are different. These are always 7 days.

/gustav
CERTIFIED EXPERT
Top Expert 2016

Commented:
then don't post a negative comment against other post..

Author

Commented:
Hi everyone,

I appreciate everyone's contribution.  I talked back to the user who is using a program her brother developed in Access that allows her to enter a date and the number of months comes up.

I looked at the select statement she sent me, but I do not know how the query works because I am new to Access 2007, and do any of you know how this query is working in access and producing the number of months.  

I could use my report in access to use this same query and get the total months.  He total months she gets when she enters a date is different from mine she is coming up with less months sometimes and sometimes we come up with the same number of months on some people.

This query is: SELECT DateDiff('m',DateAdd('m',1,[Enter Date]), #12/1/2011#) AS Total_Months from TableName

How could I setup an access database with this query and find the total months as I enter a date in?

Also, number of months is all I need.  

jjc9809
CERTIFIED EXPERT
Top Expert 2016

Commented:
do you already have a database ? or it is a new problem aside from the original problem that you posted above?

if you have an exisitng database, better that you upload it here so we can see what needs to be done.
CERTIFIED EXPERT
Most Valuable Expert 2015
Distinguished Expert 2018
Commented:
DateAdd('m',1,[Enter Date])

adds one month to Enter Date.

DateDiff('m',DateAdd('m',1,[Enter Date]), #12/1/2011#)

finds the difference between the months of the dates; this is not the same as the count of (full) months between the dates.
The same could be done this simpler way:

SELECT DateDiff('m',[Enter Date], #12/1/2011#)-1 AS Total_Months from TableName

As she asks for "Total_Months", the query should most likely calculate to count of full months. If so, use the function above like this:

SELECT Months([Enter Date]), #12/1/2011#)-1 AS Total_Months from TableName

/gustav

Commented:
SELECT DateDiff("m",StartDate,DateTo)-iif(day(StartDate)>Day(DateTo),1,0) AS TotalMonths FROM myTable;
CERTIFIED EXPERT
Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
But Ray, that will fail for many combinations involving the last days of the months.

/gustav

Author

Commented:
gustav,

SELECT Months([Enter Date]), #12/1/2011#)-1 AS Total_Months from TableName

Is this the query that you feel that would wrok best giving more accurat results.
CERTIFIED EXPERT
Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
It will give accurate results, no more no less, for general use.

Of course, you may have special business rules (like those mentioned previously) which it doesn't encounter for, but you haven't indicated that this should be the case.

/gustav

Author

Commented:
Gustav,

Each year employees receive a longevity check based on the number of months worked.  The number of years worked is used as an illustration.  For example, 0 - 5 years an employee receives $400, 5-10 years $500, 11-15 years $700, 16-20 $800 and 20-25 years $900 and over 25 years $1000.

The lady uses number of months to determine the number of years worked and she receives a breakout showing the number of months a person has worked from the main office, but she has me to develop a list for her to compare and see if the numbers match for accounting purposes.

The formula I used was:  Total Months: DateDiff('m',[StartDate],[DateTo]) but she says this formula is giving an extra month on the Total Months and her brothers formula is:  DateDiff('m',DateAdd('m',1,{enter Date]),#12/1/2011#) is used by her to enter dates to check my numbers.

I just needed a formula that would give more accurate results.

jjc9809
CERTIFIED EXPERT
Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
Thanks for the feedback!

/gustav

Explore More ContentExplore courses, solutions, and other research materials related to this topic.