Jim-
You tell me! What are dtStart and dtStop? Are they fields in the Record Source or controls on the form? What is in those two fields or controls?
John Viescas, Author
Microsoft Access 2010 Inside Out
Microsoft Access 2007 Inside Out
Microsoft Access 2003 Inside Out
Building Microsoft Access Applications
SQL Queries for Mere Mortals
(Paris, France)
On Jan 13, 2015, at 8:01 PM, Jim Wagner luvmymelody@yahoo.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:
John,
So I thought that it was dtStart and dtStop because of the control source for the text box which is
=DateDiffString([dtStart],[dtStop])
But it has been years since I had looked at that code because the text box has not had the issue for awhile.
So I am thinking that I inadvertently combined or am using the wrong code to get the results. What should I use for the control source?
Jim Wagner
On Tuesday, January 13, 2015 11:53 AM, "John Viescas JohnV@msn.com [MS_Access_Professionals]" <MS_Access_Professionals@yahoogroups.com> wrote:
Jim-
myGetTimeDiff has nothing to do with it. My function should return the correct string. What are the two dates you are passing that you think are giving you a wrong answer?
John Viescas, Author
Microsoft Access 2010 Inside Out
Microsoft Access 2007 Inside Out
Microsoft Access 2003 Inside Out
Building Microsoft Access Applications
SQL Queries for Mere Mortals
(Paris, France)
On Jan 13, 2015, at 7:11 PM, Jim Wagner luvmymelody@yahoo.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:
John,
Here is the code. the mygettimediff is the first function.
Jim
'~~~~~~~~~~~ ~~~~~~~~~ ~~~~~~~~~ ~~~~~~~~
Public Function myGetTimeDiff(ByVal dtStart As Date, dtStop As Date) As String
'
' written by Brent Spaulding
' 4-19-08
'
'Returns the difference of two moments in time in the form of a string that
'looks like this:
'
'y Years|m Months|d Days|h Hours|n Minutes|s Seconds
'
'Where y,m,d,h,n,s is the respective number that is returned for the
'labeled unit in the string.
Dim strTemp As String, dtTemp As Date
Dim lngYears As Long, lngMonths As Long, lngDays As Long
Dim lngHours As Long, lngMinutes As Long, lngSeconds As Long
Dim dblTotalTime As Double
Dim X As Integer
'Ensure the start is LESS than the stop
If DateValue(dtStart) > DateValue(dtStop) _
Or (DateValue(dtStart) = DateValue(dtStop) _
And TimeValue(dtStart) > TimeValue(dtStop)) Then
dtTemp = dtStart
dtStart = dtStop
dtStop = dtTemp
End If
'Get the years between the two dates
lngYears = DateDiff("yyyy", dtStart, dtStop)
dtTemp = DateAdd("yyyy", lngYears, dtStart)
If lngYears > 0 Then
If DateValue(dtTemp) > DateValue(dtStop) _
Or (DateValue(dtTemp) = DateValue(dtStop) _
And TimeValue(dtTemp) > TimeValue(dtStop)) Then
lngYears = lngYears - 1
End If
End If
'Get the months between the two dates that exceed the years
dtStart = DateAdd("yyyy", lngYears, dtStart)
lngMonths = DateDiff("m", dtStart, dtStop)
dtTemp = DateAdd("m", lngMonths, dtStart)
If lngMonths > 0 Then
If DateValue(dtTemp) > DateValue(dtStop) _
Or (DateValue(dtTemp) = DateValue(dtStop) _
And TimeValue(dtTemp) > TimeValue(dtStop)) Then
lngMonths = lngMonths - 1
End If
End If
'Get the number of days between the two dates that exceed the years + months ...
dtStart = DateAdd("m", lngMonths, dtStart)
lngDays = DateDiff("d", dtStart, dtStop)
If lngDays > 0 Then _
lngDays = lngDays - Abs(TimeValue(DateAdd("d", lngDays, dtTemp)) > TimeValue(dtStop))
'Build string for the "left" of our time difference
For X = 1 To 3
strTemp = strTemp & "|" & Choose(X, lngYears, lngMonths, lngDays) & _
" " & Choose(X, "Years", "Months", "Days")
Next X
'Build string for the "right" of our time difference.
dblTotalTime = (TimeValue(dtStop) - TimeValue(dtStart))
dblTotalTime = dblTotalTime + Abs(dblTotalTime < 0)
strTemp = strTemp & "|" & Format(dblTotalTime, "h Hours|" & _
"n Minutes|" & _
"s Seconds")
'Return the string
myGetTimeDiff = Mid(strTemp, 2)
End Function
'~~~~~~~~~~~ ~~~~~~~~~ ~~~~~~~~~ ~~~~~~~~
Public Function myGetTimeDiff(ByVal dtStart As Date, dtStop As Date) As String
'
' written by Brent Spaulding
' 4-19-08
'
'Returns the difference of two moments in time in the form of a string that
'looks like this:
'
'y Years|m Months|d Days|h Hours|n Minutes|s Seconds
'
'Where y,m,d,h,n,s is the respective number that is returned for the
'labeled unit in the string.
Dim strTemp As String, dtTemp As Date
Dim lngYears As Long, lngMonths As Long, lngDays As Long
Dim lngHours As Long, lngMinutes As Long, lngSeconds As Long
Dim dblTotalTime As Double
Dim X As Integer
'Ensure the start is LESS than the stop
If DateValue(dtStart) > DateValue(dtStop) _
Or (DateValue(dtStart) = DateValue(dtStop) _
And TimeValue(dtStart) > TimeValue(dtStop)) Then
dtTemp = dtStart
dtStart = dtStop
dtStop = dtTemp
End If
'Get the years between the two dates
lngYears = DateDiff("yyyy", dtStart, dtStop)
dtTemp = DateAdd("yyyy", lngYears, dtStart)
If lngYears > 0 Then
If DateValue(dtTemp) > DateValue(dtStop) _
Or (DateValue(dtTemp) = DateValue(dtStop) _
And TimeValue(dtTemp) > TimeValue(dtStop)) Then
lngYears = lngYears - 1
End If
End If
'Get the months between the two dates that exceed the years
dtStart = DateAdd("yyyy", lngYears, dtStart)
lngMonths = DateDiff("m", dtStart, dtStop)
dtTemp = DateAdd("m", lngMonths, dtStart)
If lngMonths > 0 Then
If DateValue(dtTemp) > DateValue(dtStop) _
Or (DateValue(dtTemp) = DateValue(dtStop) _
And TimeValue(dtTemp) > TimeValue(dtStop)) Then
lngMonths = lngMonths - 1
End If
End If
'Get the number of days between the two dates that exceed the years + months ...
dtStart = DateAdd("m", lngMonths, dtStart)
lngDays = DateDiff("d", dtStart, dtStop)
If lngDays > 0 Then _
lngDays = lngDays - Abs(TimeValue(DateAdd("d", lngDays, dtTemp)) > TimeValue(dtStop))
'Build string for the "left" of our time difference
For X = 1 To 3
strTemp = strTemp & "|" & Choose(X, lngYears, lngMonths, lngDays) & _
" " & Choose(X, "Years", "Months", "Days")
Next X
'Build string for the "right" of our time difference.
dblTotalTime = (TimeValue(dtStop) - TimeValue(dtStart))
dblTotalTime = dblTotalTime + Abs(dblTotalTime < 0)
strTemp = strTemp & "|" & Format(dblTotalTime, "h Hours|" & _
"n Minutes|" & _
"s Seconds")
'Return the string
myGetTimeDiff = Mid(strTemp, 2)
End Function
'~~~~~~~~~~~ ~~~~~~~~~ ~~~~~~~~~ ~~~~~~~~
'JohnViescas
Public Function DateDiffString(datFromDate As Date, datToDate As Date) As String
Dim intYears As Integer, intMonths As Integer, intDays As Integer
Dim intDayFrom As Integer
Public Function DateDiffString(datFromDate As Date, datToDate As Date) As String
Dim intYears As Integer, intMonths As Integer, intDays As Integer
Dim intDayFrom As Integer
' Calculate the years
intYears = DateDiff("yyyy", datFromDate, datToDate)
' But adjust if the month of "from" is > month of "to"
If Month(datFromDate) >= Month(datToDate) Then
intYears = intYears - 1
' .. or if months are the same but from date > to date
ElseIf Month(datFromDate) = Month(datToDate) And Day(datFromDate) > Day(datToDate) Then
intYears = intYears - 1
End If
' Calculate Months
intMonths = DateDiff("m", datFromDate, datToDate) - (intYears * 12)
' Adjust if the day of "to" is > the day of "from"
If Day(datFromDate) > Day(datToDate) Then
intMonths = intMonths - 1
End If
' Calculate Days - depends on the ending month
Select Case Month(datToDate)
' January, February, April, June, August, September, November
Case 1, 2, 4, 6, 8, 9, 11
' Previous month has full 31 days, so end day of previous is OK
intDayFrom = Day(datFromDate)
' March
Case 3
' February has 28 or 29 days, so adjust
' Check for leap year
If Month(DateSerial(Year(datToDate), 2, 29)) = 2 Then
' Leap year
If Day(datFromDate) > 29 Then
' Use 29
intDayFrom = 29
Else
' Use actual date
intDayFrom = Day(datFromDate)
End If
Else
' Not a leap year
If Day(datFromDate) > 28 Then
' Use 29
intDayFrom = 28
Else
' Use actual date
intDayFrom = Day(datFromDate)
End If
End If
' May, July, October, December
Case Else
If Day(datFromDate) > 30 Then
intDayFrom = 30
Else
intDayFrom = Day(datFromDate)
End If
End Select
' Just do a straight subtract if "from" day <= "to" day
If intDayFrom <= Day(datToDate) Then
intDays = Day(datToDate) - intDayFrom
Else
' Number of days using previous month of the "to" date
intDays = datToDate - DateSerial(Year(datToDate), Month(datToDate) - 1, intDayFrom)
End If
' Assemble the answer
DateDiffString = intYears & IIf(intYears = 1, " year, ", " years, ") & _
intMonths & IIf(intMonths = 1, " month, ", " months, ") & _
intDays & IIf(intDays = 1, " day.", " days.")
End Function
intYears = DateDiff("yyyy", datFromDate, datToDate)
' But adjust if the month of "from" is > month of "to"
If Month(datFromDate) >= Month(datToDate) Then
intYears = intYears - 1
' .. or if months are the same but from date > to date
ElseIf Month(datFromDate) = Month(datToDate) And Day(datFromDate) > Day(datToDate) Then
intYears = intYears - 1
End If
' Calculate Months
intMonths = DateDiff("m", datFromDate, datToDate) - (intYears * 12)
' Adjust if the day of "to" is > the day of "from"
If Day(datFromDate) > Day(datToDate) Then
intMonths = intMonths - 1
End If
' Calculate Days - depends on the ending month
Select Case Month(datToDate)
' January, February, April, June, August, September, November
Case 1, 2, 4, 6, 8, 9, 11
' Previous month has full 31 days, so end day of previous is OK
intDayFrom = Day(datFromDate)
' March
Case 3
' February has 28 or 29 days, so adjust
' Check for leap year
If Month(DateSerial(Year(datToDate), 2, 29)) = 2 Then
' Leap year
If Day(datFromDate) > 29 Then
' Use 29
intDayFrom = 29
Else
' Use actual date
intDayFrom = Day(datFromDate)
End If
Else
' Not a leap year
If Day(datFromDate) > 28 Then
' Use 29
intDayFrom = 28
Else
' Use actual date
intDayFrom = Day(datFromDate)
End If
End If
' May, July, October, December
Case Else
If Day(datFromDate) > 30 Then
intDayFrom = 30
Else
intDayFrom = Day(datFromDate)
End If
End Select
' Just do a straight subtract if "from" day <= "to" day
If intDayFrom <= Day(datToDate) Then
intDays = Day(datToDate) - intDayFrom
Else
' Number of days using previous month of the "to" date
intDays = datToDate - DateSerial(Year(datToDate), Month(datToDate) - 1, intDayFrom)
End If
' Assemble the answer
DateDiffString = intYears & IIf(intYears = 1, " year, ", " years, ") & _
intMonths & IIf(intMonths = 1, " month, ", " months, ") & _
intDays & IIf(intDays = 1, " day.", " days.")
End Function
Jim Wagner
On Tuesday, January 13, 2015 11:02 AM, "John Viescas JohnV@msn.com [MS_Access_Professionals]" <MS_Access_Professionals@yahoogroups.com> wrote:
Jim-
There must be another function called DateDiffString that is probably calling myGetTimeDiff.
John Viescas, Author
Microsoft Access 2010 Inside Out
Microsoft Access 2007 Inside Out
Microsoft Access 2003 Inside Out
Building Microsoft Access Applications
SQL Queries for Mere Mortals
(Paris, France)
On Jan 13, 2015, at 5:20 PM, Jim Wagner luvmymelody@yahoo.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:
Duane,
After looking at the code and the text box I am now more confused. This was sooo long ago. The text box on the form has the source of
=DateDiffString([dtStart],[dtStop])
but DateDiffString is not in the code below.
'~~~~~~~~~~~ ~~~~~~~~~ ~~~~~~~~~ ~~~~~~~~
Public Function myGetTimeDiff(ByVal dtStart As Date, dtStop As Date) As String
'
' written by Brent Spaulding
' 4-19-08
'
'Returns the difference of two moments in time in the form of a string that
'looks like this:
'
'y Years|m Months|d Days|h Hours|n Minutes|s Seconds
'
'Where y,m,d,h,n,s is the respective number that is returned for the
'labeled unit in the string.
Dim strTemp As String, dtTemp As Date
Dim lngYears As Long, lngMonths As Long, lngDays As Long
Dim lngHours As Long, lngMinutes As Long, lngSeconds As Long
Dim dblTotalTime As Double
Dim X As Integer
'Ensure the start is LESS than the stop
If DateValue(dtStart) > DateValue(dtStop) _
Or (DateValue(dtStart) = DateValue(dtStop) _
And TimeValue(dtStart) > TimeValue(dtStop)) Then
dtTemp = dtStart
dtStart = dtStop
dtStop = dtTemp
End If
'Get the years between the two dates
lngYears = DateDiff("yyyy", dtStart, dtStop)
dtTemp = DateAdd("yyyy", lngYears, dtStart)
If lngYears > 0 Then
If DateValue(dtTemp) > DateValue(dtStop) _
Or (DateValue(dtTemp) = DateValue(dtStop) _
And TimeValue(dtTemp) > TimeValue(dtStop)) Then
lngYears = lngYears - 1
End If
End If
'Get the months between the two dates that exceed the years
dtStart = DateAdd("yyyy", lngYears, dtStart)
lngMonths = DateDiff("m", dtStart, dtStop)
dtTemp = DateAdd("m", lngMonths, dtStart)
If lngMonths > 0 Then
If DateValue(dtTemp) > DateValue(dtStop) _
Or (DateValue(dtTemp) = DateValue(dtStop) _
And TimeValue(dtTemp) > TimeValue(dtStop)) Then
lngMonths = lngMonths - 1
End If
End If
'Get the number of days between the two dates that exceed the years + months ...
dtStart = DateAdd("m", lngMonths, dtStart)
lngDays = DateDiff("d", dtStart, dtStop)
If lngDays > 0 Then _
lngDays = lngDays - Abs(TimeValue(DateAdd("d", lngDays, dtTemp)) > TimeValue(dtStop))
'Build string for the "left" of our time difference
For X = 1 To 3
strTemp = strTemp & "|" & Choose(X, lngYears, lngMonths, lngDays) & _
" " & Choose(X, "Years", "Months", "Days")
Next X
'Build string for the "right" of our time difference.
dblTotalTime = (TimeValue(dtStop) - TimeValue(dtStart))
dblTotalTime = dblTotalTime + Abs(dblTotalTime < 0)
strTemp = strTemp & "|" & Format(dblTotalTime, "h Hours|" & _
"n Minutes|" & _
"s Seconds")
'Return the string
myGetTimeDiff = Mid(strTemp, 2)
End Function
'~~~~~~~~~~~ ~~~~~~~~~ ~~~~~~~~~ ~~~~~~~~
Public Function myGetTimeDiff(ByVal dtStart As Date, dtStop As Date) As String
'
' written by Brent Spaulding
' 4-19-08
'
'Returns the difference of two moments in time in the form of a string that
'looks like this:
'
'y Years|m Months|d Days|h Hours|n Minutes|s Seconds
'
'Where y,m,d,h,n,s is the respective number that is returned for the
'labeled unit in the string.
Dim strTemp As String, dtTemp As Date
Dim lngYears As Long, lngMonths As Long, lngDays As Long
Dim lngHours As Long, lngMinutes As Long, lngSeconds As Long
Dim dblTotalTime As Double
Dim X As Integer
'Ensure the start is LESS than the stop
If DateValue(dtStart) > DateValue(dtStop) _
Or (DateValue(dtStart) = DateValue(dtStop) _
And TimeValue(dtStart) > TimeValue(dtStop)) Then
dtTemp = dtStart
dtStart = dtStop
dtStop = dtTemp
End If
'Get the years between the two dates
lngYears = DateDiff("yyyy", dtStart, dtStop)
dtTemp = DateAdd("yyyy", lngYears, dtStart)
If lngYears > 0 Then
If DateValue(dtTemp) > DateValue(dtStop) _
Or (DateValue(dtTemp) = DateValue(dtStop) _
And TimeValue(dtTemp) > TimeValue(dtStop)) Then
lngYears = lngYears - 1
End If
End If
'Get the months between the two dates that exceed the years
dtStart = DateAdd("yyyy", lngYears, dtStart)
lngMonths = DateDiff("m", dtStart, dtStop)
dtTemp = DateAdd("m", lngMonths, dtStart)
If lngMonths > 0 Then
If DateValue(dtTemp) > DateValue(dtStop) _
Or (DateValue(dtTemp) = DateValue(dtStop) _
And TimeValue(dtTemp) > TimeValue(dtStop)) Then
lngMonths = lngMonths - 1
End If
End If
'Get the number of days between the two dates that exceed the years + months ...
dtStart = DateAdd("m", lngMonths, dtStart)
lngDays = DateDiff("d", dtStart, dtStop)
If lngDays > 0 Then _
lngDays = lngDays - Abs(TimeValue(DateAdd("d", lngDays, dtTemp)) > TimeValue(dtStop))
'Build string for the "left" of our time difference
For X = 1 To 3
strTemp = strTemp & "|" & Choose(X, lngYears, lngMonths, lngDays) & _
" " & Choose(X, "Years", "Months", "Days")
Next X
'Build string for the "right" of our time difference.
dblTotalTime = (TimeValue(dtStop) - TimeValue(dtStart))
dblTotalTime = dblTotalTime + Abs(dblTotalTime < 0)
strTemp = strTemp & "|" & Format(dblTotalTime, "h Hours|" & _
"n Minutes|" & _
"s Seconds")
'Return the string
myGetTimeDiff = Mid(strTemp, 2)
End Function
'~~~~~~~~~~~ ~~~~~~~~~ ~~~~~~~~~ ~~~~~~~~
Jim Wagner
On Tuesday, January 13, 2015 9:14 AM, "Duane Hookom duanehookom@hotmail.com [MS_Access_Professionals]" <MS_Access_Professionals@yahoogroups.com> wrote:
Jim,
What are the dates you are sending into the function?
Duane Hookom, MVP
MS Access
To: MS_Access_Professionals@yahoogroups.com
From: MS_Access_Professionals@yahoogroups.com
Date: Tue, 13 Jan 2015 16:04:12 +0000
Subject: Re: [MS_AccessPros] Re: Days calculations Problem
What are the dates you are sending into the function?
Duane Hookom, MVP
MS Access
To: MS_Access_Professionals@yahoogroups.com
From: MS_Access_Professionals@yahoogroups.com
Date: Tue, 13 Jan 2015 16:04:12 +0000
Subject: Re: [MS_AccessPros] Re: Days calculations Problem
John,
This is from an old post from a few years ago that I am still using. But all of a sudden the results are not showing like they used to. It started doing this at the beginning of the year.
The text box is showing the following
8 years, 12 months, 10 days
I think that it should say 9 years, 0 months, 10 days. but not sure why.
Jim Wagner
On Friday, April 2, 2010 2:08 AM, A.D. Tejpal <adtp@airtelmail.in> wrote:
Clive, John, Bill,
Apparently, any solution with user defined logic is not likely to prove completely perfect. This stems from the fact that different months are of different durations which in turn are not constant across the years. VBA has its internal style of handling calculated dates representing month ends. Intelligent rounding of the computed finish date to a month end date takes place only if the projected finish date happens to be > the actual end date for the finish month in question.
For example, adding 1 month to 31-Jan-2010 gets 28-Feb-2010. On the other hand, adding 1 month to 28-Feb-2010 fetches only 28-Mar-2010 (not 31-Mar-2010) . Similarly, adding 1 month to 31-Mar-2010 gets 30-Apr-2010, while addition of 1 month to 30-Apr-2010 gets only 30-May-2010 (not 31-May-2010) .
Going backwards, subtracting 1 month from 31-Mar-2010 gets 28-Feb-2010, while doing similar subtraction from 28-Feb-2010 fetches 28-Jan-2010 (not 31-Jan-2010) . As an interesting example, if 1 month is subtracted from 31-Mar-2010 and then 1 month is added to the result, we get 28-Mar-2010. Thus for net subtraction/ addition of zero month, the starting date value stands reduced by 3. This brings out a weird aspect of DateAdd() operation involving positive and negative values for month argument. For consistent results, it is desirable to always compute forward from lower to higher date, implying positive value for the month argument, even if the user happens to supply the dates in reverse order.
Gustav Brock, a member of another discussion group, has made a brilliant suggestion whereby we can take full advantage of VBA's built-in date arithmetic, completely eliminating the need for imposing any external logic that would otherwise need a host of conditional checks. In a nutshell, it involves use of DateAdd() function.
Sample function Fn_GetDateDiffAsYMD (), duly revised, based upon the approach recommended by Gustav, is given below. This seems to be the optimum solution for getting date difference in year-month-day format, providing results in complete conformity with built in date arithmetic of VBA. Apparent anomalies if any, as compared to a given user's concept of best results, are attributable to factors inherent to VBA's date logic, nothing to do with a developer's preference.
Some of the sample results using this proposed function as against function version 2 posted by JV are placed below for ready reference:
============ ========= ========= ========= =======
Dt1 Dt2 YMD_AD YMD_JV_2
------------ --------- --------- --------- --------- --------- -
31-Jan-2003 28-Feb-2003 1 Month 0 years, 0 months, 28 days.
31-Mar-2003 30-Apr-2003 1 Month 0 years, 0 months, 30 days.
31-May-2003 30-Jun-2003 1 Month 0 years, 0 months, 30 days.
31-May-2003 29-Feb-2004 9 Months 0 years, 8 months, 29 days.
31-May-2003 30-Jun-2004 1 Year, 1 Month 1 year, 0 months, 30 days.
31-May-2003 28-Feb-2005 1 Year, 9 Months 1 year, 8 months, 28 days.
31-May-2003 30-Apr-2005 1 Year, 11 Months 1 year, 10 months, 30 days.
20-Jan-2003 15-Jan-2003 (Minus) 5 Days -1 years, 11 months, 26 days.
28-Feb-2003 31-Jan-2003 (Minus) 1 Month -1 years, 11 months, 3 days.
============ ========= ========= ========= =======
Interested members of this group might like to carry out corroborative checks and confirm the outcome.
Best wishes,
A.D. Tejpal
------------
' Sample function for date difference as a string
' in years, months, days format
' (In total conformity with date logic of Access VBA)
'=========== ========= ========= ======
Public Function Fn_GetDateDiffAsYMD ( _
Date1 As Date, _
Optional Date2 As Variant) As String
' Returns date difference as a string of
' Years, Months, Days
' If second argument is not supplied, it defaults
' to today's date - the function then serves as
' age calculator, with first argument being
' date of birth.
' The difference is based upon time flow from
' lower to higher date. Date1 is meant to be
' the lower than Date2. If Date1 is greater than
' Date2, the result gets prefixed with "(minus) "
Dim Dt1 As Date, Dt2 As Date, Dt As Date
Dim Yr As Long, Mn As Long, Dy As Long
Dim Prefix As String
If IsMissing(Date2) Then
Dt = Date
Else
Dt = IIf(IsDate(Date2) , Date2, Date)
End If
If Dt = Date1 Then
Fn_GetDateDiffAsYMD = "NIL"
Exit Function
End If
' Set Dt2 as the greater one
If Dt > Date1 Then
Dt2 = Dt
Dt1 = Date1
Prefix = ""
Else
Dt2 = Date1
Dt1 = Dt
Prefix = "(Minus) "
End If
' Get difference in months
Mn = DateDiff("m" , Dt1, Dt2)
' Get the difference of days between Dt2
' and projected date in last completed month
' (obtained by adding Mn months to Dt1)
Dt = DateAdd("m", Mn, Dt1)
If Dt > Dt2 Then
' Step back by one month
Mn = Mn - 1
Dt = DateAdd("m", Mn, Dt1)
End If
Dy = DateDiff("d" , Dt, Dt2)
Yr = Mn \ 12
Mn = Mn Mod 12
Fn_GetDateDiffAsYMD = Prefix & _
IIf(Yr > 0, Yr & _
IIf(Yr > 1, " Years", " Year"), "") & _
IIf(Yr > 0 And Mn > 0, ", ", "") & _
IIf(Mn > 0, Mn & _
IIf(Mn > 1, " Months", " Month"), "") & _
IIf((Yr > 0 Or Mn > 0) And Dy > 0, _
", ", "") & IIf(Dy > 0, Dy & _
IIf(Dy > 1, " Days", " Day"), "")
End Function
'=========== ========= ========= ====
----- Original Message -----
From: John Viescas
To: MS_Access_Professio nals@yahoogroups .com
Sent: Thursday, April 01, 2010 16:21
Subject: RE: [MS_AccessPros] Re: Days calculations Problem
Um, I meant to say:
* The logic still breaks down when crossing a month end and the "to" day is LESS
THAN
the "from" day.
John Viescas, author
Microsoft Office Access 2007 Inside Out
Building Microsoft Access Applications
Microsoft Office Access 2003 Inside Out
SQL Queries for Mere Mortals
http://www.viescas. com/
(Paris, France)
-----Original Message-----
From: MS_Access_Professio nals@yahoogroups .com
[mailto:MS_Access_Professio nals@yahoogroups .com] On Behalf Of John Viescas
Sent: Thursday, April 01, 2010 12:42 PM
To: MS_Access_Professio nals@yahoogroups .com
Subject: RE: [MS_AccessPros] Re: Days calculations Problem
Clive-
I've modified my code to consider the last day of the start month rather than
the last day of the month previous to the end month. (Code below.) Here are my
results:
StartDate_ EndDate__- JVdiff___V1
1999-01-01 2000-02-27 1 year, 1 month, 26 days
1999-01-01 2000-02-28 1 year, 1 month, 27 days
1999-01-01 2000-02-29 1 year, 1 month, 28 days
1999-01-01 2000-03-01 1 year, 2 months, 0 days
1999-01-01 2000-03-02 1 year, 2 months, 1 day
StartDate_ EndDate__- JVdiff___V1
1998-04-05 2000-03-29 1 year, 11 months, 24 days
1998-04-05 2000-03-30 1 year, 11 months, 25 days
1998-04-05 2000-03-31 1 year, 11 months, 26 days *
1998-04-05 2000-04-01 1 year, 11 months, 26 days *
1998-04-05 2000-04-02 1 year, 11 months, 27 days
1998-04-05 2000-04-03 1 year, 11 months, 28 days
1998-04-05 2000-04-04 1 year, 11 months, 29 days
1998-04-05 2000-04-05 2 years, 0 months, 0 days
1998-04-05 2000-04-06 2 years, 0 months, 1 day
* The logic still breaks down when crossing a month end and the "to" date is >
"from" date. I basically like A.D.'s argument that when you have this case, you
count the days from the "to" date to the end of its month, count the months in
between, then add the days from the first of the "end" month to the end date.
So, 1998-04-05 to 1998-04-30 is 25 days. There is 1 year and 11 months in
between - 1998-04-30 to 2000-03-30. From the "end" of March to April 1 is 1
day, so the total days is 26.
OK, let's consider the "remaining" days after the end date of the "start" month
to the end date of the month just before the "end" month. You get this result:
StartDate_ EndDate__- JVdiff___V2
1999-01-01 2000-02-27 1 year, 1 month, 26 days
1999-01-01 2000-02-28 1 year, 1 month, 27 days
1999-01-01 2000-02-29 1 year, 1 month, 28 days
1999-01-01 2000-03-01 1 year, 2 months, 0 days
1999-01-01 2000-03-02 1 year, 2 months, 1 day
StartDate_ EndDate__- JVdiff___V2
1998-04-05 2000-03-29 1 year, 11 months, 24 days
1998-04-05 2000-03-30 1 year, 11 months, 25 days
1998-04-05 2000-03-31 1 year, 11 months, 26 days
1998-04-05 2000-04-01 1 year, 11 months, 27 days
1998-04-05 2000-04-02 1 year, 11 months, 28 days
1998-04-05 2000-04-03 1 year, 11 months, 29 days
1998-04-05 2000-04-04 1 year, 11 months, 30 days
1998-04-05 2000-04-05 2 years, 0 months, 0 days
1998-04-05 2000-04-06 2 years, 0 months, 1 day
In some ways, the second result is a bit more logical because the difference in
days increments by 1 across the range until you get to the same day number. It
all depends on what you consider the "end" of the month.
John Viescas, author
Microsoft Office Access 2007 Inside Out
Building Microsoft Access Applications
Microsoft Office Access 2003 Inside Out
SQL Queries for Mere Mortals
http://www.viescas. com/
(Paris, France)
CODE:
Public Function DateDiffString1( datFromDate As Date, datToDate As Date) As
String
Dim intYears As Integer, intMonths As Integer, intDays As Integer
Dim intDayFrom As Integer
' Calculate the years
intYears = DateDiff("yyyy" , datFromDate, datToDate)
' But adjust if the month of "from" is > month of "to"
If Month(datFromDate) >= Month(datToDate) Then
intYears = intYears - 1
' .. or if months are the same but from date > to date
ElseIf Month(datFromDate) = Month(datToDate) And Day(datFromDate) >
Day(datToDate) Then
intYears = intYears - 1
End If
' Calculate Months
intMonths = DateDiff("m" , datFromDate, datToDate) - (intYears * 12)
' Adjust if the day of "to" is > the day of "from"
If Day(datFromDate) > Day(datToDate) Then
intMonths = intMonths - 1
' Calculate Days - depends on the starting month
Select Case Month(datFromDate)
' April, June, September, November - last day is 30
Case 4, 6, 9, 11
intDayFrom = 30
' February
Case 2
' February has 28 or 29 days, so adjust
' Check for leap year
If Month(DateSerial( Year(datFromDate ), 2, 29)) = 2 Then
' Leap year
intDayFrom = 29
Else
intDayFrom = 28
End If
' January, March, May, July, August, October, December
Case Else
' Last day is 31
intDayFrom = 31
End Select
' Calculate days remaining in the "from" month
intDays = intDayFrom - Day(datFromDate)
' Add the days in the "to" month
intDays = intDays + Day(datToDate)
Else
' Extra days are the difference
intDays = Day(datToDate) - Day(datFromDate)
' But if months is exactly 12
If intMonths = 12 Then
' Add 1 to years
intYears = intYears + 1
' Set months to 0
intMonths = 0
End If
End If
' Assemble the answer
DateDiffString1 = intYears & IIf(intYears = 1, " year, ", " years, ") & _
intMonths & IIf(intMonths = 1, " month, ", " months, ") & _
intDays & IIf(intDays = 1, " day.", " days.")
End Function
Public Function DateDiffString2( datFromDate As Date, datToDate As Date) As
String
Dim intYears As Integer, intMonths As Integer, intDays As Integer
Dim intDayFrom As Integer
' Calculate the years
intYears = DateDiff("yyyy" , datFromDate, datToDate)
' But adjust if the month of "from" is > month of "to"
If Month(datFromDate) >= Month(datToDate) Then
intYears = intYears - 1
' .. or if months are the same but from date > to date
ElseIf Month(datFromDate) = Month(datToDate) And Day(datFromDate) >
Day(datToDate) Then
intYears = intYears - 1
End If
' Calculate Months
intMonths = DateDiff("m" , datFromDate, datToDate) - (intYears * 12)
' Adjust if the day of "to" is > the day of "from"
If Day(datFromDate) > Day(datToDate) Then
intMonths = intMonths - 1
' Calculate Days - depends on the starting month
Select Case Month(datFromDate)
' April, June, September, November - last day is 30
Case 4, 6, 9, 11
intDayFrom = 30
' February
Case 2
' February has 28 or 29 days, so adjust
' Check for leap year
If Month(DateSerial( Year(datFromDate ), 2, 29)) = 2 Then
' Leap year
intDayFrom = 29
Else
intDayFrom = 28
End If
' January, March, May, July, August, October, December
Case Else
' Last day is 31
intDayFrom = 31
End Select
' Calculate days remaining in the "from" month
intDays = intDayFrom - Day(datFromDate)
' If the end day of the month prior to the "end" date
' is greater than the end date of the "from" date,
If Day(DateSerial( Year(datToDate) , Month(datToDate) , 1) - 1) _
> intDayFrom Then
' Add the remaining days
intDays = intDays + (Day(DateSerial( Year(datToDate) , _
Month(datToDate) , 1) - 1) - intDayFrom)
End If
' Add the days in the "to" month
intDays = intDays + Day(datToDate)
Else
' Extra days are the difference
intDays = Day(datToDate) - Day(datFromDate)
' But if months is exactly 12
If intMonths = 12 Then
' Add 1 to years
intYears = intYears + 1
' Set months to 0
intMonths = 0
End If
End If
' Assemble the answer
DateDiffString2 = intYears & IIf(intYears = 1, " year, ", " years, ") & _
intMonths & IIf(intMonths = 1, " month, ", " months, ") & _
intDays & IIf(intDays = 1, " day.", " days.")
End Function
-----Original Message-----
From: MS_Access_Professio nals@yahoogroups .com
[mailto:MS_Access_Professio nals@yahoogroups .com] On Behalf Of horastacatto
Sent: Wednesday, March 31, 2010 11:58 PM
To: MS_Access_Professio nals@yahoogroups .com
Subject: [MS_AccessPros] Re: Days calculations Problem
Hi AD, Bill, John,
I'm sorry, I don't understand what that clause is doing.
It may be necessary for some combination of StartDate and
EndDate but at the moment there are what I consider to be
bad results for many combinations. Maybe you have not come
across them in your testing?
Here are two examples of StartDates with various EndDates.
The ones with a '*' I regard as incorrect. Due you agree?
StartDate_ EndDate__- ADdiff___
1999-01-01 2000-02-27 001-01-26
1999-01-01 2000-02-28 001-01-27
1999-01-01 2000-02-29 001-01-30 *
1999-01-01 2000-03-01 001-02-00
1999-01-01 2000-03-02 001-02-01
StartDate_ EndDate__- ADdiff___
1998-04-05 2000-03-29 001-11-24
1998-04-05 2000-03-30 001-11-25
1998-04-05 2000-03-31 001-11-25 * This one is also a dup.
1998-04-05 2000-04-01 001-11-26 *
1998-04-05 2000-04-02 001-11-27 *
1998-04-05 2000-04-03 001-11-28 *
1998-04-05 2000-04-04 001-11-29 *
1998-04-05 2000-04-05 002-00-00
1998-04-05 2000-04-06 002-00-01
Do you get the same results as I do?
I am in the UK but I would expect the Windows
Regional and Language Settings to take care of it.
Regards, Clive.
--- In MS_Access_Professio nals@yahoogroups .com, "A.D. Tejpal" <adtp@...> wrote:
>
> Clive,
>
> This is explained by clause 4 of the underlying logic mentioned in my post
dated 17-Mar-2010. It reads as follows:
> ============ ========= =======
> "4 - If Dy2 is at the end of month and Dy1 is not, Dy equals unfinished days
in start month."
> ============ ========= =======
>
> In fact I was hoping that based upon suggestions from members of this
group, some consensus could be evolved on the underlying principles to be
followed for computing the date difference.
>
> It would therefore be nice if you could kindly examine the 9 clauses of
proposed logic as submitted in my post of 17-Mar-2010 and advise as to the
modifications felt necessary. Similar inputs from other interested members would
be most welcome. Thereafter, based upon the agreed set of principles, the
underlying function could be updated accordingly.
>
> Best wishes,
> A.D. Tejpal
> ------------
>
> ----- Original Message -----
> From: horastacatto
> To: MS_Access_Professio nals@yahoogroups .com
> Sent: Tuesday, March 30, 2010 14:27
> Subject: Re: [MS_AccessPros] Days calculations Problem
>
>
> Hi AD, Bill, John,
>
> I am still looking at this in my spare time.
> I have built a testbed to look at various
> combinations of dates.
>
> AD, this is a result that shows up a difference
> between John's and your's. I would like to know
> why you show 002-01-30 rather than 002-01-27 in
> the middle row. Is it deliberate or accidental?
>
> StartDate_ EndDate___ JVdiff___ ADdiff___ Mismatch
> 1999-01-01 2001-02-27 002-01-26 002-01-26 0
> 1999-01-01 2001-02-28 002-01-27 002-01-30 -1
> 1999-01-01 2001-03-01 002-02-00 002-02-00 0
>
> I think it might be the underlyiing cause of other
> errors such as the 31st of the month returning the
> same value as the 30th of the month for the seven
> instances in each year (I have not tried debugging it).
> You can show up the problem using,
> ?Fn_GetDateDiffAsYM D(dateserial( 2000,04,24) , _
> DateSerial(2000, 05,30))
> 1 Month, 6 Days
> ?Fn_GetDateDiffAsYM D(dateserial( 2000,04,24) , _
> DateSerial(2000, 05,31))
> 1 Month, 6 Days
>
> Regards, Clive.
>
> --- In MS_Access_Professio nals@yahoogroups .com, "Bill Mosca" <wrmosca@>
wrote:
> >
> > A.D.
> >
> > Sorry I didn't comment sooner. I think you are looking at months the way I
do...as calendar blocks of days rather than John's method which sees one month
between the same dates of consecutive months as in his example:
> > > ?DateDiffString( #2/28/1999# , #3/28/1999#)
> > > 0 years, 1 month, 0 days.
> >
> > To me that is 28 days, not 1 month. But no matter what I think, which
method to use must be decided either by the application designer or by the
client and made known up front and remain consistant throughout the application.
> >
> > Bill
> >
> >
> > --- In MS_Access_Professio nals@yahoogroups .com, "A.D. Tejpal" <adtejpal@>
wrote:
> > >
> > > John, Bill, Clive,
> > >
> > > Not sure whether my post of 17-Mar-2010 (copy placed below) got
connected at your end. It would be nice if we could evolve a consensus on some
common grounds for evaluating the date difference as completed years, months and
days.
> > >
> > > Your views (as well as those of other interested members in this
group) on the proposed logic, as outlined in my post, would be most welcome.
> > >
> > > Best wishes,
> > > A.D. Tejpal
> > > ------------
> > >
> > > ----- Original Message -----
> > > From: A.D. Tejpal
> > > To: MS_Access_Professio nals@yahoogroups .com
> > > Sent: Wednesday, March 17, 2010 14:32
> > > Subject: Re: [MS_AccessPros] Days calculations Problem
> > >
> > >
> > > John, Bill, Clive,
> > >
> > > The very fact that the days in various months & years happen to
vary, makes it necessary to optimize the calculations in such a manner as to
provide the best possible results over a broad spectrum of date combinations.
> > >
> > > Let us examine the specific dates mentioned in John's post, i.e.
29-Sep-1999 to 16-Mar-2010. The result are as follows (For ready comparison, the
outcome using the function suggested by John is placed directly below the one
obtained by using my proposed function):
> > >
> > > 29-Sep-1999 : 16-Mar-2010 = 10 Years, 5 Months, 17 Days (AD)
> > > 10 years, 5 months,
16 days. (JV)
> > >
> > > Explanation:
> > > ------------
> > > If neither of the two dates represent end of respective months and
if day part of end date is less than that of start date, the days in result get
computed by adding unfinished days in start month and elapsed days in end month.
Thus 29-Sep-1999 to 30-Sep-1999 gives us 1 day. Thereafter, 30-sep-1999 to
30-Sep-2009 gets us 10 years. Following that, we get 5 completed months till end
of Feb-2010. This leaves us with 16 elapsed days in Mar-2010, which added to 1
unfinished day of Sep-1999 comes to 17 days.
> > >
> > > For ready appreciation of the nature of results obtained via the
two alternative functions, some sample results arising out of different date
combinations are given below:
> > > ============ ========= ========= ========= ======
> > > 29-Sep-1999 : 28-Mar-2010 = 10 Years, 5 Months, 29 Days (AD)
> > > 10 years, 5 months,
0 days. (JV)
> > >
> > > 29-Sep-1999 : 29-Mar-2010 = 10 Years, 6 Months (AD)
> > > 10 years, 6 months,
1 day. (JV)
> > >
> > > 29-Sep-1999 : 30-Mar-2010 = 10 Years, 6 Months, 1 Day (AD)
> > > 10 years, 6 months,
2 days. (JV)
> > >
> > > 29-Sep-1999 : 31-Mar-2010 = 10 Years, 6 Months, 1 Day (AD)
> > > 10 years, 6 months,
3 days. (JV)
> > >
> > > 30-Sep-1999 : 16-Mar-2010 = 10 Years, 5 Months, 16 Days (AD)
> > > 10 years, 5 months,
16 days. (JV)
> > >
> > > 30-Sep-1999 : 28-Mar-2010 = 10 Years, 5 Months, 28 Days (AD)
> > > 10 years, 5 months,
0 days. (JV)
> > >
> > > 30-Sep-1999 : 29-Mar-2010 = 10 Years, 5 Months, 29 Days (AD)
> > > 10 years, 5 months,
1 day. (JV)
> > >
> > > 30-Sep-1999 : 30-Mar-2010 = 10 Years, 5 Months, 30 Days (AD)
> > > 10 years, 6 months,
2 days. (JV)
> > >
> > > 30-Sep-1999 : 31-Mar-2010 = 10 Years, 6 Months (AD)
> > > 10 years, 6 months,
3 days. (JV)
> > > ============ ========= ========= ========= =====
> > >
> > > For ready reference, the underlying logic used in my function is
clarified below:
> > >
------------ --------- --------- --------- --------- --------- -
> > > 1 - Get total count of months Mn by using built in DateDiff()
function.
> > > 2 - Subtract 1 from Mn if day part of end date (Dy2) is less than
day part of start date (Dy1).
> > > 3 - If both Dy1 & Dy2 represent ends of respective months, day
part of the result (Dy) is zero.
> > > 4 - If Dy2 is at the end of month and Dy1 is not, Dy equals
unfinished days in start month.
> > > 5 - In 3 & 4 above, if Dy2 happens to be less than Dy1, month
count Mn gets compensated by adding 1 to it.
> > > 6 - If Dy1 is at the end of month and Dy2 is not, Dy equals Dy2.
Moreover, if Dy2 >= Dy1, month count Mn gets adjusted by subtracting 1 from it.
> > > 7 - If neither date represents end of the month, and Dy2 >= Dy1,
result days (Dy) = Dy2 - Dy1.
> > > 8 - If neither date represents end of the month, and Dy2 < Dy1,
result days (Dy) is computed by adding Dy2 and unfinished days in start month.
> > > 9 - Eventually, after the month count (Mn) has been duly
compensated as necessary, the elapsed years (Yr) are computed as Mn\12, and
result months = Mn Mod 12.
> > >
> > > In the interest of evolving the most optimum universal solution,
any suggestions for modifying or further fine tuning the above logic, would be
welcome.
> > >
> > > Best wishes,
> > > A.D. Tejpal
> > > ------------
> > >
> > > ----- Original Message -----
> > > From: Bill Mosca
> > > To: MS_Access_Professio nals@yahoogroups .com
> > > Sent: Wednesday, March 17, 2010 07:52
> > > Subject: RE: [MS_AccessPros] Days calculations Problem
> > >
> > >
> > > John
> > >
> > > Maybe I'm splitting hairs here but there is no such thing as a month
as a means
> > > of measurement. And while I'm at it, a year is not a consistent
quantity either.
> > > That is unless one capitulates to a month being 30 days and a year
being 365
> > > days or maybe 12 months. I don't choose to do so. The only thing you
can break
> > > it all down to are seconds, being the lowest general dominator for
time.
> > >
> > > Regards,
> > >
> > > Bill
> > >
> > >
> > > From: MS_Access_Professio nals@yahoogroups .com
> > > [mailto:MS_Access_Professio nals@yahoogroups .com] On Behalf Of John
Viescas
> > > Sent: Tuesday, March 16, 2010 3:10 PM
> > > To: MS_Access_Professio nals@yahoogroups .com
> > > Subject: RE: [MS_AccessPros] Days calculations Problem
> > >
> > >
> > >
> > >
> > >
> > > Bill-
> > >
> > > As I pointed out in a post early in this thread, DateDiff cannot be
trusted for
> > > "yyyy" and "m" arguments.
> > >
> > > So, today is the 16th of March. What day is one month from today? If
today is
> > > February 28, what day is one month after that?
> > >
> > > John Viescas, author
> > > Microsoft Office Access 2007 Inside Out
> > > Building Microsoft Access Applications
> > > Microsoft Office Access 2003 Inside Out
> > > SQL Queries for Mere Mortals
> > > http://www.viescas. com/
> > > (Paris, France)
> > >
> > > -----Original Message-----
> > > From: MS_Access_Professio nals@yahoogroups .com
> > > <mailto:MS_Access_ Professionals% 40yahoogroups. com>
> > > [mailto:MS_Access_Professio nals@yahoogroups .com
> > > <mailto:MS_Access_ Professionals% 40yahoogroups. com> ] On Behalf Of
Bill Mosca
> > > Sent: Tuesday, March 16, 2010 10:29 PM
> > > To: MS_Access_Professio nals@yahoogroups .com
> > > <mailto:MS_Access_ Professionals% 40yahoogroups. com>
> > > Subject: Re: [MS_AccessPros] Days calculations Problem
> > >
> > > John
> > >
> > > You're not convincing me. <s> Just because a
> > > DateDiff("m" ,#9/29/1999# ,#10/29/1999# ) returns one month doesn't
mean it is
> > > correct. A month is not a measure of time. It is a varied block of
days on the
> > > calendar. As you said, a month can have 28, 29, 30 or 31 days in it.
Contrarily,
> > > dollar always has 100 cents in it.
> > >
> > > Bill
> > >
> > > --- In MS_Access_Professio nals@yahoogroups .com
> > > <mailto:MS_Access_ Professionals% 40yahoogroups. com> , "John Viescas"
<JohnV@>
> > > wrote:
> > > >
> > > > Bill-
> > > >
> > > > Both A.D. and I would have to disagree.
> > > >
> > > > ?DateDiffString( #9/29/1999# , #10/29/1999# )
> > > > 0 years, 1 month, 0 days.
> > > > ?FN_GetDateDiffAsYM D(#9/29/1999# , #10/29/1999# )
> > > > 1 Month
> > > >
> > > > I would argue that the span between any day in one month to the
same day in
> > > the
> > > > next month is exactly one month. You have a problem, however, with
the days
> > > 29,
> > > > 30, and 31 because not all months have those days. My function
calculates one
> > > > month as the span between the last day of the month previous to
the last month
> > > > and the same day in the next month.
> > > >
> > > > ?DateDiffString( #2/28/1999# , #3/28/1999#)
> > > > 0 years, 1 month, 0 days.
> > > >
> > > > ?DateDiffString( #2/28/1999# , #3/29/1999#)
> > > > 0 years, 1 month, 1 day.
> > > >
> > > > ?DateDiffString( #1/28/1999# , #2/28/1999#)
> > > > 0 years, 1 month, 0 days.
> > > >
> > > > If the ending month has fewer days than the previous month, then
you get a
> > > days
> > > > calculation.
> > > >
> > > > ?DateDiffString( #1/29/1999# , #2/28/1999#)
> > > > 0 years, 0 months, 30 days.
> > > >
> > > > ?DateDiffString( #1/30/1999# , #2/28/1999#)
> > > > 0 years, 0 months, 29 days.
> > > >
> > > > A.D.'s function takes a different approach:
> > > >
> > > > ?FN_GetDateDiffAsYM D(#1/30/1999# , #2/28/1999#)
> > > > 1 Month, 1 Day
> > > >
> > > > .. and I disagree with that result.
> > > >
> > > > John Viescas, author
> > > > Microsoft Office Access 2007 Inside Out
> > > > Building Microsoft Access Applications
> > > > Microsoft Office Access 2003 Inside Out
> > > > SQL Queries for Mere Mortals
> > > > http://www.viescas. com/
> > > > (Paris, France)
> > > >
> > > >
> > > > -----Original Message-----
> > > > From: MS_Access_Professio nals@yahoogroups .com
> > > <mailto:MS_Access_ Professionals% 40yahoogroups. com>
> > > > [mailto:MS_Access_Professio nals@yahoogroups .com
> > > <mailto:MS_Access_ Professionals% 40yahoogroups. com> ] On Behalf Of
Bill Mosca
> > > > Sent: Tuesday, March 16, 2010 9:10 PM
> > > > To: MS_Access_Professio nals@yahoogroups .com
> > > <mailto:MS_Access_ Professionals% 40yahoogroups. com>
> > > > Subject: Re: [MS_AccessPros] Days calculations Problem
> > > >
> > > > Philosophically speaking, if you are dealing with 3 parts each
must be taken
> > > as
> > > > a whole unit. It is not like currency in which 100 cents always
makes a exact
> > > > equivalent of one dollar (at least before taxes <g>)
> > > >
> > > > September 29th 1999 to October 29th 1999 is not a month. It is
> > > > 30 days. One month has not gone by. Since October has 31 days that
is not a
> > > > complete calendar month difference. Moving into November would
give you the
> > > > entire month of October.
> > > >
> > > > This holds true for days as well since a day is a calendar unit.
24 hrs does
> > > not
> > > > make a day. A day starts right at midnight (00:00:0000) and ends
just before
> > > the
> > > > next midnight (23:59:9999) .
> > > >
> > > > Taking my theory to the span of 9/29/1999 to 3/16/2010 you have to
start with
> > > > 10/1/1999 and end with 2/28/2010 which is 10 years, 4 months.
There is one day
> > > > before October 1999 and 16 days into March 2010. That leaves a net
of 15 days,
> > > > right?
> > > >
> > > > Bill
> > > >
> > > > --- In MS_Access_Professio nals@yahoogroups .com
> > > <mailto:MS_Access_ Professionals% 40yahoogroups. com> , "John Viescas"
<JohnV@>
> > > > wrote:
> > > > >
> > > > > A.D.-
> > > > >
> > > > > I find there's a slight difference between your function and
mine:
> > > > >
> > > > > ?FN_GetDateDiffAsYM D(#9/29/1999# )
> > > > > 10 Years, 5 Months, 17 Days
> > > > >
> > > > > ?DateDiffString( #9/29/1999# , Date())
> > > > > 10 years, 5 months, 16 days.
> > > > > 62 years, 5 months, 16 days.
> > > > >
> > > > > I would argue that since September 29 occurs at the end of a
month and
> > > > February
> > > > > has only 28 days, the 10 years and 5 months is correct, but my
calculation
> > > of
> > > > 16
> > > > > days - we are 16 days in to March - is more valid than yours.
Comments?
> > > > >
> > > > > John Viescas, author
> > > > > Microsoft Office Access 2007 Inside Out
> > > > > Building Microsoft Access Applications
> > > > > Microsoft Office Access 2003 Inside Out
> > > > > SQL Queries for Mere Mortals
> > > > > http://www.viescas. com/
> > > > > (Paris, France)
> > > > >
> > > > >
> > > > > -----Original Message-----
> > > > > From: MS_Access_Professio nals@yahoogroups .com
> > > <mailto:MS_Access_ Professionals% 40yahoogroups. com>
> > > > > [mailto:MS_Access_Professio nals@yahoogroups .com
> > > <mailto:MS_Access_ Professionals% 40yahoogroups. com> ] On Behalf Of
A.D. Tejpal
> > > > > Sent: Tuesday, March 16, 2010 2:16 PM
> > > > > To: MS_Access_Professio nals@yahoogroups .com
> > > <mailto:MS_Access_ Professionals% 40yahoogroups. com>
> > > > > Subject: Re: [MS_AccessPros] Days calculations Problem
> > > > >
> > > > > John, Jim,
> > > > >
> > > > > Feasibility of a universal function for calculation of date
difference
> > > in
> > > > > terms of completed years, months and days has often featured in
various
> > > > > discussion groups. It is also referred as age calculation
function when
> > > second
> > > > > argument is optional and defaults to today's date.
> > > > >
> > > > > Over the years, different flavors of such a function have been
> > > attempted.
> > > > It
> > > > > has generally been felt that an optimum solution would be one
that could
> > > > handle
> > > > > various combinations of date arguments in such a manner as to
provide sample
> > > > > results in the following manner:
> > > > >
> > > > > (a) 28-Feb-2006 to 29-Feb-2008 = 2 Years
> > > > > (b) 28-Feb-2006 to 28-Feb-2008 = 2 Years
> > > > > (c) 29-Feb-2008 to 28-Feb-2010 = 2 Years
> > > > > (d) 28-Feb-2008 to 28-Feb-2010 = 2 Years
> > > > > (e) 28-Feb-2006 to 29-Mar-2008 = 2 Years, 29 Days
> > > > > (f) 30-Apr-2007 to 30-May-2009 = 2 Years, 30 Days
> > > > > (g) 30-Apr-2007 to 31-May-2009 = 2 Years, 1 Month
> > > > > (h) 31-Mar-2005 to 30-Mar-2007 = 1 Year, 11 Months, 30 Days
> > > > > (i) 31-Mar-2005 to 31-Mar-2007 = 2 Years
> > > > >
> > > > > Apparently, a universal solution, capable of delivering
consistent
> > > results
> > > > > in above manner, has so far remained elusive. Sample function
named
> > > > > Fn_GetDateDiffAsYMD () as given below, is an attempt to fulfill
this
> > > > requirement.
> > > > > The second argument is optional and if not supplied, it defaults
to today's
> > > > date
> > > > > - the function then serves as age calculator, with first
argument being date
> > > > of
> > > > > birth.
> > > > >
> > > > > It would be nice, if interested members of this group could
carry out
> > > > > corroborative tests and confirm whether anything needs further
attention.
> > > > >
> > > > > Best wishes,
> > > > > A.D. Tejpal
> > > > > ------------
> > > > >
> > > > > ' Sample function for calculating date difference
> > > > > ' as Years, Months, days
> > > > > '=========== ========= ========= ==
> > > > > Public Function Fn_GetDateDiffAsYMD ( _
> > > > > DtFrom As Date, _
> > > > > Optional DtUpto As Variant) As String
> > > > > ' Returns date difference as a string of
> > > > > ' Years, Months, Days
> > > > > ' If second argument is not supplied, it defaults
> > > > > ' to today's date - the function then serves as
> > > > > ' age calculator, with first argument being
> > > > > ' date of birth.
> > > > > Dim Dt1 As Date, Dt2 As Date
> > > > > Dim Dy1 As Long, Dy2 As Long
> > > > > Dim Yr As Long, Mn As Long, Dy As Long
> > > > >
> > > > > Dt1 = DtFrom
> > > > > If IsMissing(DtUpto) Then
> > > > > Dt2 = Date
> > > > > Else
> > > > > Dt2 = IIf(IsDate(DtUpto) , DtUpto, Date)
> > > > > End If
> > > > >
> > > > > Dy1 = Day(Dt1)
> > > > > Dy2 = Day(Dt2)
> > > > >
> > > > > ' Get number of completed months
> > > > > Mn = DateDiff("m" , Dt1, Dt2) - _
> > > > > IIf(Dy2 < Dy1, 1, 0) ' (A)
> > > > >
> > > > > If Day(Dt1 + 1) = 1 Then
> > > > > ' Dt1 is last day of the month
> > > > > If Day(Dt2 + 1) = 1 Then
> > > > > ' Dt2 is last day of the month
> > > > > Dy = 0
> > > > > ' Undo the subtraction at (A)
> > > > > Mn = Mn + IIf(Dy2 < Dy1, 1, 0)
> > > > > Else
> > > > > If Format(Dt2, "mmdd") = _
> > > > > Format(Dt1, "mmdd") Then
> > > > > Dy = 0
> > > > > Else
> > > > > Dy = Dy2
> > > > > Mn = Mn - IIf(Dy2 >= Dy1, 1, 0)
> > > > > End If
> > > > > End If
> > > > > Else
> > > > > ' Balance days in starting month
> > > > > Dy = Day(DateSerial( Year(Dt1) , _
> > > > > Month(Dt1) + 1, 0)) - Dy1
> > > > > If Day(Dt2 + 1) = 1 Then
> > > > > ' Dt2 is last day of the month
> > > > > If Dy2 = Dy1 Then
> > > > > Dy = 0
> > > > > End If
> > > > > ' Undo the subtraction at (A)
> > > > > Mn = Mn + IIf(Dy2 < Dy1, 1, 0)
> > > > > Else
> > > > > If Dy2 < Dy1 Then
> > > > > Dy = Dy + Dy2
> > > > > Else
> > > > > Dy = Dy2 - Dy1
> > > > > End If
> > > > > End If
> > > > > End If
> > > > >
> > > > > Yr = Mn \ 12
> > > > > Mn = Mn Mod 12
> > > > >
> > > > > Fn_GetDateDiffAsYMD = IIf(Yr > 0, Yr & _
> > > > > IIf(Yr > 1, " Years", " Year"), "") & _
> > > > > IIf(Yr > 0 And Mn > 0, ", ", "") & _
> > > > > IIf(Mn > 0, Mn & _
> > > > > IIf(Mn > 1, " Months", " Month"), "") & _
> > > > > IIf((Yr > 0 Or Mn > 0) And Dy > 0, _
> > > > > ", ", "") & IIf(Dy > 0, Dy & _
> > > > > IIf(Dy > 1, " Days", " Day"), "")
> > > > > End Function
> > > > > '=========== ========= ========= =====
> > > > >
> > > > > ----- Original Message -----
> > > > > From: John Viescas
> > > > > To: MS_Access_Professio nals@yahoogroups .com
> > > <mailto:MS_Access_ Professionals% 40yahoogroups. com>
> > > > > Sent: Tuesday, March 16, 2010 12:30
> > > > > Subject: RE: [MS_AccessPros] Days calculations Problem
> > > > >
> > > > >
> > > > > Jim-
> > > > >
> > > > > It's a bug. This works:
> > > > >
> > > > > Public Function DateDiffString( datFromDate As Date, datToDate As
Date) As
> > > > > String
> > > > > Dim intYears As Integer, intMonths As Integer, intDays As
Integer
> > > > > Dim intDayFrom As Integer
> > > > >
> > > > > ' Calculate the years
> > > > > intYears = DateDiff("yyyy" , datFromDate, datToDate)
> > > > > ' But adjust if the month of "from" is > month of "to"
> > > > > If Month(datFromDate) >= Month(datToDate) Then
> > > > > intYears = intYears - 1
> > > > > ' .. or if months are the same but from date > to date
> > > > > ElseIf Month(datFromDate) = Month(datToDate) And
Day(datFromDate) >
> > > > > Day(datToDate) Then
> > > > > intYears = intYears - 1
> > > > > End If
> > > > > ' Calculate Months
> > > > > intMonths = DateDiff("m" , datFromDate, datToDate) - (intYears *
12)
> > > > > ' Adjust if the day of "to" is > the day of "from"
> > > > > If Day(datFromDate) > Day(datToDate) Then
> > > > > intMonths = intMonths - 1
> > > > > End If
> > > > > ' Calculate Days - depends on the ending month
> > > > > Select Case Month(datToDate)
> > > > > ' January, February, April, June, August, September, November
> > > > > Case 1, 2, 4, 6, 8, 9, 11
> > > > > ' Previous month has full 31 days, so end day of previous is OK
> > > > > intDayFrom = Day(datFromDate)
> > > > > ' March
> > > > > Case 3
> > > > > ' February has 28 or 29 days, so adjust
> > > > > ' Check for leap year
> > > > > If Month(DateSerial( Year(datToDate) , 2, 29)) = 2 Then
> > > > > ' Leap year
> > > > > If Day(datFromDate) > 29 Then
> > > > > ' Use 29
> > > > > intDayFrom = 29
> > > > > Else
> > > > > ' Use actual date
> > > > > intDayFrom = Day(datFromDate)
> > > > > End If
> > > > > Else
> > > > > ' Not a leap year
> > > > > If Day(datFromDate) > 28 Then
> > > > > ' Use 29
> > > > > intDayFrom = 28
> > > > > Else
> > > > > ' Use actual date
> > > > > intDayFrom = Day(datFromDate)
> > > > > End If
> > > > > End If
> > > > > ' May, July, October, December
> > > > > Case Else
> > > > > If Day(datFromDate) > 30 Then
> > > > > intDayFrom = 30
> > > > > Else
> > > > > intDayFrom = Day(datFromDate)
> > > > > End If
> > > > > End Select
> > > > > ' Just do a straight subtract if "from" day <= "to" day
> > > > > If intDayFrom <= Day(datToDate) Then
> > > > > intDays = Day(datToDate) - intDayFrom
> > > > > Else
> > > > > ' Number of days using previous month of the "to" date
> > > > > intDays = datToDate - DateSerial(Year( datToDate) ,
Month(datToDate) -
> > > 1,
> > > > > intDayFrom)
> > > > > End If
> > > > > ' Assemble the answer
> > > > > DateDiffString = intYears & IIf(intYears = 1, " year, ", "
years, ") & _
> > > > > intMonths & IIf(intMonths = 1, " month, ", " months, ") & _
> > > > > intDays & IIf(intDays = 1, " day.", " days.")
> > > > > End Function
> > > > >
> > > > > John Viescas, author
> > > > > Microsoft Office Access 2007 Inside Out
> > > > > Building Microsoft Access Applications
> > > > > Microsoft Office Access 2003 Inside Out
> > > > > SQL Queries for Mere Mortals
> > > > > http://www.viescas. com/
> > > > > (Paris, France)
> > > > >
> > > > >
> > > > > -----Original Message-----
> > > > > From: MS_Access_Professio nals@yahoogroups .com
> > > <mailto:MS_Access_ Professionals% 40yahoogroups. com>
> > > > > [mailto:MS_Access_Professio nals@yahoogroups .com
> > > <mailto:MS_Access_ Professionals% 40yahoogroups. com> ] On Behalf Of
Jim Wagner
> > > > > Sent: Monday, March 15, 2010 11:31 PM
> > > > > To: MS_Access_Professio nals@yahoogroups .com
> > > <mailto:MS_Access_ Professionals% 40yahoogroups. com>
> > > > > Subject: Re: [MS_AccessPros] Days calculations Problem
> > > > >
> > > > > Ok, I thought that this was working. I just looked at my form
and I have
> > > > > 1/3/2006 as my start date and the results are
> > > > > 4 years, 2 months, 40 days. Is there a tweak that I need to make
to get it
> > > > > correct?
> > > > >
> > > > > Jim Wagner
> > > > > ____________ _________ _________ __
> > > > >
> > > > >
> > > > >
> > > > >
> > > > >
> > > > > ____________ _________ _________ __
> > > > > From: John Viescas <JohnV@>
> > > > > To: MS_Access_Professio nals@yahoogroups .com
> > > <mailto:MS_Access_ Professionals% 40yahoogroups. com>
> > > > > Sent: Tue, February 23, 2010 3:37:14 AM
> > > > > Subject: RE: [MS_AccessPros] Days calculations Problem
> > > > >
> > > > >
> > > > > Ahmed-
> > > > >
> > > > > Sure. Try this:
> > > > >
> > > > > Public Function CalcDays(varConfirm ation As Variant,
varResignation As
> > > > > Variant)
> > > > > As Integer
> > > > > Dim datDateFrom As Date, datDateTo As Date, datTest As Date
> > > > >
> > > > > ' First, make sure there's a date in the first argument
> > > > > If Not IsDate(varConfirmat ion) Then
> > > > > ' Nope - return -1 to indicate error
> > > > > CalcDays = -1
> > > > > Exit Function
> > > > > End If
> > > > > ' Use the year of the first argument to calculate the first
comparison
> > > date
> > > > > datTest = DateSerial(Year( varConfirmation) , 7, 1)
> > > > > ' If confirmation date is less than July 1, use it
> > > > > If varConfirmation < datTest Then
> > > > > datDateFrom = varConfirmation
> > > > > Else
> > > > > ' Otherwise, use July 1
> > > > > datDateFrom = datTest
> > > > > End If
> > > > > ' If the resignation date is not a null
> > > > > If Not IsNull(varResignati on) Then
> > > > > ' Use the resignation date
> > > > > datDateTo = varResignation
> > > > > Else
> > > > > ' Figure out end of current fiscal year
> > > > > ' If confirmation is less than July 1
> > > > > If varConfirmation < datTest Then
> > > > > ' Use June 30 of confirmation year
> > > > > datDateTo = DateSerial(Year( varConfirmation) , 6, 30)
> > > > > Else
> > > > > ' Use June 30 of next year
> > > > > datDateTo = DateSerial(Year( varConfirmation) + 1, 6, 30)
> > > > > End If
> > > > > End If
> > > > > ' Return the answer
> > > > > CalcDays = DateDiff("d" , datDateFrom, datDateTo)
> > > > > End Function
> > > > >
> > > > > John Viescas, author
> > > > > Microsoft Office Access 2007 Inside Out
> > > > > Building Microsoft Access Applications
> > > > > Microsoft Office Access 2003 Inside Out
> > > > > SQL Queries for Mere Mortals
> > > > > http://www.viescas. com/
> > > > > (Paris, France)
> > > > >
> > > > > -----Original Message-----
> > > > > From: MS_Access_Professio nals@yahoogroups .com
> > > > > [mailto:MS_Access_ Professio nals@yahoogroups .com] On Behalf Of
Ahmed
> > > Hashim
> > > > > Sent: Tuesday, February 23, 2010 11:00 AM
> > > > > To: MS_Access_Professio nals@yahoogroups .com
> > > > > Subject: Re: [MS_AccessPros] Days calculations Problem
> > > > >
> > > > > John-
> > > > >
> > > > > Not working... :( it calculates wrong number of days....If
possible can u
> > > > > please create a vba function that do the same...???
> > > > >
> > > > > On Tue, Feb 23, 2010 at 2:33 PM, John Viescas <JohnV@viescas.
com> wrote:
> > > > >
> > > > > >
> > > > > >
> > > > > > I probably missed a paren somewhere.
> > > > > >
> > > > > >
> > > > > > DateDiff("d" ,
> > > > > > IIf([DateOfConfirma tion] < DateSerial(Year( [DateOfConfirmat
ion]), 7,
> > > > 1),
> > > > > > [DateOfConfirmation ],
> > > > > > DateSerial(Year( [DateOfConfirmat ion]), 7, 1)),
> > > > > > IIf(Not IsNull([DateOfResig nation]), [DateOfResignation] ,
> > > > > > IIf([DateOfConfirma tion] < DateSerial(Year( [DateOfConfirmat
ion]), 7,
> > > > 1),
> > > > > > DateSerial(Year( [DateOfConfirmat ion]), 6, 30),
> > > > > > DateSerial(Year( [DateOfConfirmat ion])+1, 6, 30)))
> > > > > >
> > > > > > Yeah, I missed at least 5 close parens. Try the above.
> > > > > >
> > > > > >
> > > > > > John Viescas, author
> > > > > > Microsoft Office Access 2007 Inside Out
> > > > > > Building Microsoft Access Applications
> > > > > > Microsoft Office Access 2003 Inside Out
> > > > > > SQL Queries for Mere Mortals
> > > > > > http://www.viescas. com/
> > > > > > (Paris, France)
> > > > > >
> > > > > >
> > > > > > -----Original Message-----
> > > > > > From:
> > > > > MS_Access_Professio nals@yahoogroups .com<MS_Access_ Professi
> > > > onals%40yahoogro
> > > > > ups.co
> > > > > m>
> > > > > >
> > > > > [mailto:MS_Access_ Professio nals@yahoogroups
.com<MS_Access_ Professi
> > > > > onals%40yahoog
> > > > > roups.com>]
> > > > > > On Behalf Of Ahmed Hashim
> > > > > > Sent: Tuesday, February 23, 2010 10:03 AM
> > > > > > To:
> > > > > MS_Access_Professio nals@yahoogroups .com<MS_Access_ Professi
> > > > onals%40yahoogro
> > > > > ups.co
> > > > > m>
> > > > > > Subject: Re: [MS_AccessPros] Days calculations Problem
> > > > > >
> > > > > > John-
> > > > > >
> > > > > > I think there is some syntax error in solution...? ?
> > > > > >
> > > > > > On Tue, Feb 23, 2010 at 1:27 PM, johnvmvp
> > > > > <JohnV@viescas. com<JohnV%40viescas . com>>
> > > > > > wrote:
> > > > > >
> > > > > > >
> > > > > > >
> > > > > > > Ahmed-
> > > > > > >
> > > > > > > Perhaps like this:
> > > > > > >
> > > > > > > DateDiff("d" , IIf([DateOfConfirma tion] <
> > > > > > > DateSerial(Year( [DateOfConfirmat ion], 7, 1),
[DateOfConfirmation ],
> > > > > > > DateSerial(Year( [DateOfConfirmat ion], 7, 1)), IIf(Not
> > > > > > > IsNull([DateOfResig nation]), [DateOfResignation] ,
> > > > > > IIf([DateOfConfirma tion] <
> > > > > > > DateSerial(Year( [DateOfConfirmat ion], 7, 1),
> > > > > > > DateSerial(Year( [DateOfConfirmat ion], 6, 30),
> > > > > > > DateSerial(Year( [DateOfConfirmat ion]+1, 6, 30)))
> > > > > > >
> > > > > > > John Viescas, author
> > > > > > > Microsoft Office Access 2007 Inside Out
> > > > > > > Building Microsoft Access Applications
> > > > > > > Microsoft Office Access 2003 Inside Out
> > > > > > > SQL Queries for Mere Mortals
> > > > > > > http://www.viescas. com/
> > > > > > > (Paris, France)
> > > > > > >
> > > > > > >
> > > > > > > --- In
> > > > > >
> > > > > MS_Access_Professio nals@yahoogroups .com<MS_Access_ Professi
> > > > onals%40yahoogro
> > > > > ups.co
> > > > > m>
> > > > > > <MS_Access_Professi onals%40yahoogro ups.co
> > > > > > m>,
> > > > > >
> > > > > > > Ahmed Hashim <ahmedhashim1@ ...> wrote:
> > > > > > > >
> > > > > > > > Dear All Experts,
> > > > > > > >
> > > > > > > > After a long time I am in need of your suggestions.
> > > > > > > >
> > > > > > > > I have a table with following structure:
> > > > > > > >
> > > > > > > > Emp ID (Text)
> > > > > > > > Emp Name (Text)
> > > > > > > > Date of Confirmation (Date/Time)
> > > > > > > > Date of Resignation (Date/Time)
> > > > > > > >
> > > > > > > >
> > > > > > > > I need to calculate number of days between financial year
starting
> > > > from
> > > > > > > > 01-Jul-2007 to 30-Jun-2008. I tried to use DateDiff
Function but due
> > > > to
> > > > > > > > complexities of crietiras I don't get the desired results.
> > > Followings
> > > > > > are
> > > > > > > > the criterias:
> > > > > > > >
> > > > > > > >
> > > > > > > > 1.If Employee's Date of Confirmation is less then
01-Jul-2007 then
> > > > > > > > 01-Jul-2007 will be used as Start Date(Date1 of DateDiff
functions
> > > > > > > > parameter). Example: if employees date of confirmation is
> > > 01-Jan-2006
> > > > > > > then
> > > > > > > > Date1 should be 01-Jul-2007.
> > > > > > > >
> > > > > > > > 2. Date of resignation will need to be checked, if it is
not blank
> > > > > > means
> > > > > > > > employee resigned during the year, so Date2 need to be
Date of
> > > > > > > Resignation
> > > > > > > > of Employee, otherwise Date2 will be 30-06-2008.
> > > > > > > >
> > > > > > > > Is it possible to do it with DateDiff function in
Ms.Access Query,
> > > or
> > > > > > > there
> > > > > > > > is need to create VBA function hich I've tried and failed!
:(
> > > > > > > >
> > > > > > > > By the way I am using Ms.Access ver 2000 (still!)
> > > > > > > >
> > > > > > > > An early help in this regard will be highly appreciated.
..
> > > > > > > >
> > > > > > > > thanks in advance
> > > > > > > >
> > > > > > > > Ahmed
[Non-text portions of this message have been removed]
Apparently, any solution with user defined logic is not likely to prove completely perfect. This stems from the fact that different months are of different durations which in turn are not constant across the years. VBA has its internal style of handling calculated dates representing month ends. Intelligent rounding of the computed finish date to a month end date takes place only if the projected finish date happens to be > the actual end date for the finish month in question.
For example, adding 1 month to 31-Jan-2010 gets 28-Feb-2010. On the other hand, adding 1 month to 28-Feb-2010 fetches only 28-Mar-2010 (not 31-Mar-2010) . Similarly, adding 1 month to 31-Mar-2010 gets 30-Apr-2010, while addition of 1 month to 30-Apr-2010 gets only 30-May-2010 (not 31-May-2010) .
Going backwards, subtracting 1 month from 31-Mar-2010 gets 28-Feb-2010, while doing similar subtraction from 28-Feb-2010 fetches 28-Jan-2010 (not 31-Jan-2010) . As an interesting example, if 1 month is subtracted from 31-Mar-2010 and then 1 month is added to the result, we get 28-Mar-2010. Thus for net subtraction/ addition of zero month, the starting date value stands reduced by 3. This brings out a weird aspect of DateAdd() operation involving positive and negative values for month argument. For consistent results, it is desirable to always compute forward from lower to higher date, implying positive value for the month argument, even if the user happens to supply the dates in reverse order.
Gustav Brock, a member of another discussion group, has made a brilliant suggestion whereby we can take full advantage of VBA's built-in date arithmetic, completely eliminating the need for imposing any external logic that would otherwise need a host of conditional checks. In a nutshell, it involves use of DateAdd() function.
Sample function Fn_GetDateDiffAsYMD (), duly revised, based upon the approach recommended by Gustav, is given below. This seems to be the optimum solution for getting date difference in year-month-day format, providing results in complete conformity with built in date arithmetic of VBA. Apparent anomalies if any, as compared to a given user's concept of best results, are attributable to factors inherent to VBA's date logic, nothing to do with a developer's preference.
Some of the sample results using this proposed function as against function version 2 posted by JV are placed below for ready reference:
============ ========= ========= ========= =======
Dt1 Dt2 YMD_AD YMD_JV_2
------------ --------- --------- --------- --------- --------- -
31-Jan-2003 28-Feb-2003 1 Month 0 years, 0 months, 28 days.
31-Mar-2003 30-Apr-2003 1 Month 0 years, 0 months, 30 days.
31-May-2003 30-Jun-2003 1 Month 0 years, 0 months, 30 days.
31-May-2003 29-Feb-2004 9 Months 0 years, 8 months, 29 days.
31-May-2003 30-Jun-2004 1 Year, 1 Month 1 year, 0 months, 30 days.
31-May-2003 28-Feb-2005 1 Year, 9 Months 1 year, 8 months, 28 days.
31-May-2003 30-Apr-2005 1 Year, 11 Months 1 year, 10 months, 30 days.
20-Jan-2003 15-Jan-2003 (Minus) 5 Days -1 years, 11 months, 26 days.
28-Feb-2003 31-Jan-2003 (Minus) 1 Month -1 years, 11 months, 3 days.
============ ========= ========= ========= =======
Interested members of this group might like to carry out corroborative checks and confirm the outcome.
Best wishes,
A.D. Tejpal
------------
' Sample function for date difference as a string
' in years, months, days format
' (In total conformity with date logic of Access VBA)
'=========== ========= ========= ======
Public Function Fn_GetDateDiffAsYMD ( _
Date1 As Date, _
Optional Date2 As Variant) As String
' Returns date difference as a string of
' Years, Months, Days
' If second argument is not supplied, it defaults
' to today's date - the function then serves as
' age calculator, with first argument being
' date of birth.
' The difference is based upon time flow from
' lower to higher date. Date1 is meant to be
' the lower than Date2. If Date1 is greater than
' Date2, the result gets prefixed with "(minus) "
Dim Dt1 As Date, Dt2 As Date, Dt As Date
Dim Yr As Long, Mn As Long, Dy As Long
Dim Prefix As String
If IsMissing(Date2) Then
Dt = Date
Else
Dt = IIf(IsDate(Date2) , Date2, Date)
End If
If Dt = Date1 Then
Fn_GetDateDiffAsYMD = "NIL"
Exit Function
End If
' Set Dt2 as the greater one
If Dt > Date1 Then
Dt2 = Dt
Dt1 = Date1
Prefix = ""
Else
Dt2 = Date1
Dt1 = Dt
Prefix = "(Minus) "
End If
' Get difference in months
Mn = DateDiff("m" , Dt1, Dt2)
' Get the difference of days between Dt2
' and projected date in last completed month
' (obtained by adding Mn months to Dt1)
Dt = DateAdd("m", Mn, Dt1)
If Dt > Dt2 Then
' Step back by one month
Mn = Mn - 1
Dt = DateAdd("m", Mn, Dt1)
End If
Dy = DateDiff("d" , Dt, Dt2)
Yr = Mn \ 12
Mn = Mn Mod 12
Fn_GetDateDiffAsYMD = Prefix & _
IIf(Yr > 0, Yr & _
IIf(Yr > 1, " Years", " Year"), "") & _
IIf(Yr > 0 And Mn > 0, ", ", "") & _
IIf(Mn > 0, Mn & _
IIf(Mn > 1, " Months", " Month"), "") & _
IIf((Yr > 0 Or Mn > 0) And Dy > 0, _
", ", "") & IIf(Dy > 0, Dy & _
IIf(Dy > 1, " Days", " Day"), "")
End Function
'=========== ========= ========= ====
----- Original Message -----
From: John Viescas
To: MS_Access_Professio nals@yahoogroups .com
Sent: Thursday, April 01, 2010 16:21
Subject: RE: [MS_AccessPros] Re: Days calculations Problem
Um, I meant to say:
* The logic still breaks down when crossing a month end and the "to" day is LESS
THAN
the "from" day.
John Viescas, author
Microsoft Office Access 2007 Inside Out
Building Microsoft Access Applications
Microsoft Office Access 2003 Inside Out
SQL Queries for Mere Mortals
http://www.viescas. com/
(Paris, France)
-----Original Message-----
From: MS_Access_Professio nals@yahoogroups .com
[mailto:MS_Access_Professio nals@yahoogroups .com] On Behalf Of John Viescas
Sent: Thursday, April 01, 2010 12:42 PM
To: MS_Access_Professio nals@yahoogroups .com
Subject: RE: [MS_AccessPros] Re: Days calculations Problem
Clive-
I've modified my code to consider the last day of the start month rather than
the last day of the month previous to the end month. (Code below.) Here are my
results:
StartDate_ EndDate__- JVdiff___V1
1999-01-01 2000-02-27 1 year, 1 month, 26 days
1999-01-01 2000-02-28 1 year, 1 month, 27 days
1999-01-01 2000-02-29 1 year, 1 month, 28 days
1999-01-01 2000-03-01 1 year, 2 months, 0 days
1999-01-01 2000-03-02 1 year, 2 months, 1 day
StartDate_ EndDate__- JVdiff___V1
1998-04-05 2000-03-29 1 year, 11 months, 24 days
1998-04-05 2000-03-30 1 year, 11 months, 25 days
1998-04-05 2000-03-31 1 year, 11 months, 26 days *
1998-04-05 2000-04-01 1 year, 11 months, 26 days *
1998-04-05 2000-04-02 1 year, 11 months, 27 days
1998-04-05 2000-04-03 1 year, 11 months, 28 days
1998-04-05 2000-04-04 1 year, 11 months, 29 days
1998-04-05 2000-04-05 2 years, 0 months, 0 days
1998-04-05 2000-04-06 2 years, 0 months, 1 day
* The logic still breaks down when crossing a month end and the "to" date is >
"from" date. I basically like A.D.'s argument that when you have this case, you
count the days from the "to" date to the end of its month, count the months in
between, then add the days from the first of the "end" month to the end date.
So, 1998-04-05 to 1998-04-30 is 25 days. There is 1 year and 11 months in
between - 1998-04-30 to 2000-03-30. From the "end" of March to April 1 is 1
day, so the total days is 26.
OK, let's consider the "remaining" days after the end date of the "start" month
to the end date of the month just before the "end" month. You get this result:
StartDate_ EndDate__- JVdiff___V2
1999-01-01 2000-02-27 1 year, 1 month, 26 days
1999-01-01 2000-02-28 1 year, 1 month, 27 days
1999-01-01 2000-02-29 1 year, 1 month, 28 days
1999-01-01 2000-03-01 1 year, 2 months, 0 days
1999-01-01 2000-03-02 1 year, 2 months, 1 day
StartDate_ EndDate__- JVdiff___V2
1998-04-05 2000-03-29 1 year, 11 months, 24 days
1998-04-05 2000-03-30 1 year, 11 months, 25 days
1998-04-05 2000-03-31 1 year, 11 months, 26 days
1998-04-05 2000-04-01 1 year, 11 months, 27 days
1998-04-05 2000-04-02 1 year, 11 months, 28 days
1998-04-05 2000-04-03 1 year, 11 months, 29 days
1998-04-05 2000-04-04 1 year, 11 months, 30 days
1998-04-05 2000-04-05 2 years, 0 months, 0 days
1998-04-05 2000-04-06 2 years, 0 months, 1 day
In some ways, the second result is a bit more logical because the difference in
days increments by 1 across the range until you get to the same day number. It
all depends on what you consider the "end" of the month.
John Viescas, author
Microsoft Office Access 2007 Inside Out
Building Microsoft Access Applications
Microsoft Office Access 2003 Inside Out
SQL Queries for Mere Mortals
http://www.viescas. com/
(Paris, France)
CODE:
Public Function DateDiffString1( datFromDate As Date, datToDate As Date) As
String
Dim intYears As Integer, intMonths As Integer, intDays As Integer
Dim intDayFrom As Integer
' Calculate the years
intYears = DateDiff("yyyy" , datFromDate, datToDate)
' But adjust if the month of "from" is > month of "to"
If Month(datFromDate) >= Month(datToDate) Then
intYears = intYears - 1
' .. or if months are the same but from date > to date
ElseIf Month(datFromDate) = Month(datToDate) And Day(datFromDate) >
Day(datToDate) Then
intYears = intYears - 1
End If
' Calculate Months
intMonths = DateDiff("m" , datFromDate, datToDate) - (intYears * 12)
' Adjust if the day of "to" is > the day of "from"
If Day(datFromDate) > Day(datToDate) Then
intMonths = intMonths - 1
' Calculate Days - depends on the starting month
Select Case Month(datFromDate)
' April, June, September, November - last day is 30
Case 4, 6, 9, 11
intDayFrom = 30
' February
Case 2
' February has 28 or 29 days, so adjust
' Check for leap year
If Month(DateSerial( Year(datFromDate ), 2, 29)) = 2 Then
' Leap year
intDayFrom = 29
Else
intDayFrom = 28
End If
' January, March, May, July, August, October, December
Case Else
' Last day is 31
intDayFrom = 31
End Select
' Calculate days remaining in the "from" month
intDays = intDayFrom - Day(datFromDate)
' Add the days in the "to" month
intDays = intDays + Day(datToDate)
Else
' Extra days are the difference
intDays = Day(datToDate) - Day(datFromDate)
' But if months is exactly 12
If intMonths = 12 Then
' Add 1 to years
intYears = intYears + 1
' Set months to 0
intMonths = 0
End If
End If
' Assemble the answer
DateDiffString1 = intYears & IIf(intYears = 1, " year, ", " years, ") & _
intMonths & IIf(intMonths = 1, " month, ", " months, ") & _
intDays & IIf(intDays = 1, " day.", " days.")
End Function
Public Function DateDiffString2( datFromDate As Date, datToDate As Date) As
String
Dim intYears As Integer, intMonths As Integer, intDays As Integer
Dim intDayFrom As Integer
' Calculate the years
intYears = DateDiff("yyyy" , datFromDate, datToDate)
' But adjust if the month of "from" is > month of "to"
If Month(datFromDate) >= Month(datToDate) Then
intYears = intYears - 1
' .. or if months are the same but from date > to date
ElseIf Month(datFromDate) = Month(datToDate) And Day(datFromDate) >
Day(datToDate) Then
intYears = intYears - 1
End If
' Calculate Months
intMonths = DateDiff("m" , datFromDate, datToDate) - (intYears * 12)
' Adjust if the day of "to" is > the day of "from"
If Day(datFromDate) > Day(datToDate) Then
intMonths = intMonths - 1
' Calculate Days - depends on the starting month
Select Case Month(datFromDate)
' April, June, September, November - last day is 30
Case 4, 6, 9, 11
intDayFrom = 30
' February
Case 2
' February has 28 or 29 days, so adjust
' Check for leap year
If Month(DateSerial( Year(datFromDate ), 2, 29)) = 2 Then
' Leap year
intDayFrom = 29
Else
intDayFrom = 28
End If
' January, March, May, July, August, October, December
Case Else
' Last day is 31
intDayFrom = 31
End Select
' Calculate days remaining in the "from" month
intDays = intDayFrom - Day(datFromDate)
' If the end day of the month prior to the "end" date
' is greater than the end date of the "from" date,
If Day(DateSerial( Year(datToDate) , Month(datToDate) , 1) - 1) _
> intDayFrom Then
' Add the remaining days
intDays = intDays + (Day(DateSerial( Year(datToDate) , _
Month(datToDate) , 1) - 1) - intDayFrom)
End If
' Add the days in the "to" month
intDays = intDays + Day(datToDate)
Else
' Extra days are the difference
intDays = Day(datToDate) - Day(datFromDate)
' But if months is exactly 12
If intMonths = 12 Then
' Add 1 to years
intYears = intYears + 1
' Set months to 0
intMonths = 0
End If
End If
' Assemble the answer
DateDiffString2 = intYears & IIf(intYears = 1, " year, ", " years, ") & _
intMonths & IIf(intMonths = 1, " month, ", " months, ") & _
intDays & IIf(intDays = 1, " day.", " days.")
End Function
-----Original Message-----
From: MS_Access_Professio nals@yahoogroups .com
[mailto:MS_Access_Professio nals@yahoogroups .com] On Behalf Of horastacatto
Sent: Wednesday, March 31, 2010 11:58 PM
To: MS_Access_Professio nals@yahoogroups .com
Subject: [MS_AccessPros] Re: Days calculations Problem
Hi AD, Bill, John,
I'm sorry, I don't understand what that clause is doing.
It may be necessary for some combination of StartDate and
EndDate but at the moment there are what I consider to be
bad results for many combinations. Maybe you have not come
across them in your testing?
Here are two examples of StartDates with various EndDates.
The ones with a '*' I regard as incorrect. Due you agree?
StartDate_ EndDate__- ADdiff___
1999-01-01 2000-02-27 001-01-26
1999-01-01 2000-02-28 001-01-27
1999-01-01 2000-02-29 001-01-30 *
1999-01-01 2000-03-01 001-02-00
1999-01-01 2000-03-02 001-02-01
StartDate_ EndDate__- ADdiff___
1998-04-05 2000-03-29 001-11-24
1998-04-05 2000-03-30 001-11-25
1998-04-05 2000-03-31 001-11-25 * This one is also a dup.
1998-04-05 2000-04-01 001-11-26 *
1998-04-05 2000-04-02 001-11-27 *
1998-04-05 2000-04-03 001-11-28 *
1998-04-05 2000-04-04 001-11-29 *
1998-04-05 2000-04-05 002-00-00
1998-04-05 2000-04-06 002-00-01
Do you get the same results as I do?
I am in the UK but I would expect the Windows
Regional and Language Settings to take care of it.
Regards, Clive.
--- In MS_Access_Professio nals@yahoogroups .com, "A.D. Tejpal" <adtp@...> wrote:
>
> Clive,
>
> This is explained by clause 4 of the underlying logic mentioned in my post
dated 17-Mar-2010. It reads as follows:
> ============ ========= =======
> "4 - If Dy2 is at the end of month and Dy1 is not, Dy equals unfinished days
in start month."
> ============ ========= =======
>
> In fact I was hoping that based upon suggestions from members of this
group, some consensus could be evolved on the underlying principles to be
followed for computing the date difference.
>
> It would therefore be nice if you could kindly examine the 9 clauses of
proposed logic as submitted in my post of 17-Mar-2010 and advise as to the
modifications felt necessary. Similar inputs from other interested members would
be most welcome. Thereafter, based upon the agreed set of principles, the
underlying function could be updated accordingly.
>
> Best wishes,
> A.D. Tejpal
> ------------
>
> ----- Original Message -----
> From: horastacatto
> To: MS_Access_Professio nals@yahoogroups .com
> Sent: Tuesday, March 30, 2010 14:27
> Subject: Re: [MS_AccessPros] Days calculations Problem
>
>
> Hi AD, Bill, John,
>
> I am still looking at this in my spare time.
> I have built a testbed to look at various
> combinations of dates.
>
> AD, this is a result that shows up a difference
> between John's and your's. I would like to know
> why you show 002-01-30 rather than 002-01-27 in
> the middle row. Is it deliberate or accidental?
>
> StartDate_ EndDate___ JVdiff___ ADdiff___ Mismatch
> 1999-01-01 2001-02-27 002-01-26 002-01-26 0
> 1999-01-01 2001-02-28 002-01-27 002-01-30 -1
> 1999-01-01 2001-03-01 002-02-00 002-02-00 0
>
> I think it might be the underlyiing cause of other
> errors such as the 31st of the month returning the
> same value as the 30th of the month for the seven
> instances in each year (I have not tried debugging it).
> You can show up the problem using,
> ?Fn_GetDateDiffAsYM D(dateserial( 2000,04,24) , _
> DateSerial(2000, 05,30))
> 1 Month, 6 Days
> ?Fn_GetDateDiffAsYM D(dateserial( 2000,04,24) , _
> DateSerial(2000, 05,31))
> 1 Month, 6 Days
>
> Regards, Clive.
>
> --- In MS_Access_Professio nals@yahoogroups .com, "Bill Mosca" <wrmosca@>
wrote:
> >
> > A.D.
> >
> > Sorry I didn't comment sooner. I think you are looking at months the way I
do...as calendar blocks of days rather than John's method which sees one month
between the same dates of consecutive months as in his example:
> > > ?DateDiffString( #2/28/1999# , #3/28/1999#)
> > > 0 years, 1 month, 0 days.
> >
> > To me that is 28 days, not 1 month. But no matter what I think, which
method to use must be decided either by the application designer or by the
client and made known up front and remain consistant throughout the application.
> >
> > Bill
> >
> >
> > --- In MS_Access_Professio nals@yahoogroups .com, "A.D. Tejpal" <adtejpal@>
wrote:
> > >
> > > John, Bill, Clive,
> > >
> > > Not sure whether my post of 17-Mar-2010 (copy placed below) got
connected at your end. It would be nice if we could evolve a consensus on some
common grounds for evaluating the date difference as completed years, months and
days.
> > >
> > > Your views (as well as those of other interested members in this
group) on the proposed logic, as outlined in my post, would be most welcome.
> > >
> > > Best wishes,
> > > A.D. Tejpal
> > > ------------
> > >
> > > ----- Original Message -----
> > > From: A.D. Tejpal
> > > To: MS_Access_Professio nals@yahoogroups .com
> > > Sent: Wednesday, March 17, 2010 14:32
> > > Subject: Re: [MS_AccessPros] Days calculations Problem
> > >
> > >
> > > John, Bill, Clive,
> > >
> > > The very fact that the days in various months & years happen to
vary, makes it necessary to optimize the calculations in such a manner as to
provide the best possible results over a broad spectrum of date combinations.
> > >
> > > Let us examine the specific dates mentioned in John's post, i.e.
29-Sep-1999 to 16-Mar-2010. The result are as follows (For ready comparison, the
outcome using the function suggested by John is placed directly below the one
obtained by using my proposed function):
> > >
> > > 29-Sep-1999 : 16-Mar-2010 = 10 Years, 5 Months, 17 Days (AD)
> > > 10 years, 5 months,
16 days. (JV)
> > >
> > > Explanation:
> > > ------------
> > > If neither of the two dates represent end of respective months and
if day part of end date is less than that of start date, the days in result get
computed by adding unfinished days in start month and elapsed days in end month.
Thus 29-Sep-1999 to 30-Sep-1999 gives us 1 day. Thereafter, 30-sep-1999 to
30-Sep-2009 gets us 10 years. Following that, we get 5 completed months till end
of Feb-2010. This leaves us with 16 elapsed days in Mar-2010, which added to 1
unfinished day of Sep-1999 comes to 17 days.
> > >
> > > For ready appreciation of the nature of results obtained via the
two alternative functions, some sample results arising out of different date
combinations are given below:
> > > ============ ========= ========= ========= ======
> > > 29-Sep-1999 : 28-Mar-2010 = 10 Years, 5 Months, 29 Days (AD)
> > > 10 years, 5 months,
0 days. (JV)
> > >
> > > 29-Sep-1999 : 29-Mar-2010 = 10 Years, 6 Months (AD)
> > > 10 years, 6 months,
1 day. (JV)
> > >
> > > 29-Sep-1999 : 30-Mar-2010 = 10 Years, 6 Months, 1 Day (AD)
> > > 10 years, 6 months,
2 days. (JV)
> > >
> > > 29-Sep-1999 : 31-Mar-2010 = 10 Years, 6 Months, 1 Day (AD)
> > > 10 years, 6 months,
3 days. (JV)
> > >
> > > 30-Sep-1999 : 16-Mar-2010 = 10 Years, 5 Months, 16 Days (AD)
> > > 10 years, 5 months,
16 days. (JV)
> > >
> > > 30-Sep-1999 : 28-Mar-2010 = 10 Years, 5 Months, 28 Days (AD)
> > > 10 years, 5 months,
0 days. (JV)
> > >
> > > 30-Sep-1999 : 29-Mar-2010 = 10 Years, 5 Months, 29 Days (AD)
> > > 10 years, 5 months,
1 day. (JV)
> > >
> > > 30-Sep-1999 : 30-Mar-2010 = 10 Years, 5 Months, 30 Days (AD)
> > > 10 years, 6 months,
2 days. (JV)
> > >
> > > 30-Sep-1999 : 31-Mar-2010 = 10 Years, 6 Months (AD)
> > > 10 years, 6 months,
3 days. (JV)
> > > ============ ========= ========= ========= =====
> > >
> > > For ready reference, the underlying logic used in my function is
clarified below:
> > >
------------ --------- --------- --------- --------- --------- -
> > > 1 - Get total count of months Mn by using built in DateDiff()
function.
> > > 2 - Subtract 1 from Mn if day part of end date (Dy2) is less than
day part of start date (Dy1).
> > > 3 - If both Dy1 & Dy2 represent ends of respective months, day
part of the result (Dy) is zero.
> > > 4 - If Dy2 is at the end of month and Dy1 is not, Dy equals
unfinished days in start month.
> > > 5 - In 3 & 4 above, if Dy2 happens to be less than Dy1, month
count Mn gets compensated by adding 1 to it.
> > > 6 - If Dy1 is at the end of month and Dy2 is not, Dy equals Dy2.
Moreover, if Dy2 >= Dy1, month count Mn gets adjusted by subtracting 1 from it.
> > > 7 - If neither date represents end of the month, and Dy2 >= Dy1,
result days (Dy) = Dy2 - Dy1.
> > > 8 - If neither date represents end of the month, and Dy2 < Dy1,
result days (Dy) is computed by adding Dy2 and unfinished days in start month.
> > > 9 - Eventually, after the month count (Mn) has been duly
compensated as necessary, the elapsed years (Yr) are computed as Mn\12, and
result months = Mn Mod 12.
> > >
> > > In the interest of evolving the most optimum universal solution,
any suggestions for modifying or further fine tuning the above logic, would be
welcome.
> > >
> > > Best wishes,
> > > A.D. Tejpal
> > > ------------
> > >
> > > ----- Original Message -----
> > > From: Bill Mosca
> > > To: MS_Access_Professio nals@yahoogroups .com
> > > Sent: Wednesday, March 17, 2010 07:52
> > > Subject: RE: [MS_AccessPros] Days calculations Problem
> > >
> > >
> > > John
> > >
> > > Maybe I'm splitting hairs here but there is no such thing as a month
as a means
> > > of measurement. And while I'm at it, a year is not a consistent
quantity either.
> > > That is unless one capitulates to a month being 30 days and a year
being 365
> > > days or maybe 12 months. I don't choose to do so. The only thing you
can break
> > > it all down to are seconds, being the lowest general dominator for
time.
> > >
> > > Regards,
> > >
> > > Bill
> > >
> > >
> > > From: MS_Access_Professio nals@yahoogroups .com
> > > [mailto:MS_Access_Professio nals@yahoogroups .com] On Behalf Of John
Viescas
> > > Sent: Tuesday, March 16, 2010 3:10 PM
> > > To: MS_Access_Professio nals@yahoogroups .com
> > > Subject: RE: [MS_AccessPros] Days calculations Problem
> > >
> > >
> > >
> > >
> > >
> > > Bill-
> > >
> > > As I pointed out in a post early in this thread, DateDiff cannot be
trusted for
> > > "yyyy" and "m" arguments.
> > >
> > > So, today is the 16th of March. What day is one month from today? If
today is
> > > February 28, what day is one month after that?
> > >
> > > John Viescas, author
> > > Microsoft Office Access 2007 Inside Out
> > > Building Microsoft Access Applications
> > > Microsoft Office Access 2003 Inside Out
> > > SQL Queries for Mere Mortals
> > > http://www.viescas. com/
> > > (Paris, France)
> > >
> > > -----Original Message-----
> > > From: MS_Access_Professio nals@yahoogroups .com
> > > <mailto:MS_Access_ Professionals% 40yahoogroups. com>
> > > [mailto:MS_Access_Professio nals@yahoogroups .com
> > > <mailto:MS_Access_ Professionals% 40yahoogroups. com> ] On Behalf Of
Bill Mosca
> > > Sent: Tuesday, March 16, 2010 10:29 PM
> > > To: MS_Access_Professio nals@yahoogroups .com
> > > <mailto:MS_Access_ Professionals% 40yahoogroups. com>
> > > Subject: Re: [MS_AccessPros] Days calculations Problem
> > >
> > > John
> > >
> > > You're not convincing me. <s> Just because a
> > > DateDiff("m" ,#9/29/1999# ,#10/29/1999# ) returns one month doesn't
mean it is
> > > correct. A month is not a measure of time. It is a varied block of
days on the
> > > calendar. As you said, a month can have 28, 29, 30 or 31 days in it.
Contrarily,
> > > dollar always has 100 cents in it.
> > >
> > > Bill
> > >
> > > --- In MS_Access_Professio nals@yahoogroups .com
> > > <mailto:MS_Access_ Professionals% 40yahoogroups. com> , "John Viescas"
<JohnV@>
> > > wrote:
> > > >
> > > > Bill-
> > > >
> > > > Both A.D. and I would have to disagree.
> > > >
> > > > ?DateDiffString( #9/29/1999# , #10/29/1999# )
> > > > 0 years, 1 month, 0 days.
> > > > ?FN_GetDateDiffAsYM D(#9/29/1999# , #10/29/1999# )
> > > > 1 Month
> > > >
> > > > I would argue that the span between any day in one month to the
same day in
> > > the
> > > > next month is exactly one month. You have a problem, however, with
the days
> > > 29,
> > > > 30, and 31 because not all months have those days. My function
calculates one
> > > > month as the span between the last day of the month previous to
the last month
> > > > and the same day in the next month.
> > > >
> > > > ?DateDiffString( #2/28/1999# , #3/28/1999#)
> > > > 0 years, 1 month, 0 days.
> > > >
> > > > ?DateDiffString( #2/28/1999# , #3/29/1999#)
> > > > 0 years, 1 month, 1 day.
> > > >
> > > > ?DateDiffString( #1/28/1999# , #2/28/1999#)
> > > > 0 years, 1 month, 0 days.
> > > >
> > > > If the ending month has fewer days than the previous month, then
you get a
> > > days
> > > > calculation.
> > > >
> > > > ?DateDiffString( #1/29/1999# , #2/28/1999#)
> > > > 0 years, 0 months, 30 days.
> > > >
> > > > ?DateDiffString( #1/30/1999# , #2/28/1999#)
> > > > 0 years, 0 months, 29 days.
> > > >
> > > > A.D.'s function takes a different approach:
> > > >
> > > > ?FN_GetDateDiffAsYM D(#1/30/1999# , #2/28/1999#)
> > > > 1 Month, 1 Day
> > > >
> > > > .. and I disagree with that result.
> > > >
> > > > John Viescas, author
> > > > Microsoft Office Access 2007 Inside Out
> > > > Building Microsoft Access Applications
> > > > Microsoft Office Access 2003 Inside Out
> > > > SQL Queries for Mere Mortals
> > > > http://www.viescas. com/
> > > > (Paris, France)
> > > >
> > > >
> > > > -----Original Message-----
> > > > From: MS_Access_Professio nals@yahoogroups .com
> > > <mailto:MS_Access_ Professionals% 40yahoogroups. com>
> > > > [mailto:MS_Access_Professio nals@yahoogroups .com
> > > <mailto:MS_Access_ Professionals% 40yahoogroups. com> ] On Behalf Of
Bill Mosca
> > > > Sent: Tuesday, March 16, 2010 9:10 PM
> > > > To: MS_Access_Professio nals@yahoogroups .com
> > > <mailto:MS_Access_ Professionals% 40yahoogroups. com>
> > > > Subject: Re: [MS_AccessPros] Days calculations Problem
> > > >
> > > > Philosophically speaking, if you are dealing with 3 parts each
must be taken
> > > as
> > > > a whole unit. It is not like currency in which 100 cents always
makes a exact
> > > > equivalent of one dollar (at least before taxes <g>)
> > > >
> > > > September 29th 1999 to October 29th 1999 is not a month. It is
> > > > 30 days. One month has not gone by. Since October has 31 days that
is not a
> > > > complete calendar month difference. Moving into November would
give you the
> > > > entire month of October.
> > > >
> > > > This holds true for days as well since a day is a calendar unit.
24 hrs does
> > > not
> > > > make a day. A day starts right at midnight (00:00:0000) and ends
just before
> > > the
> > > > next midnight (23:59:9999) .
> > > >
> > > > Taking my theory to the span of 9/29/1999 to 3/16/2010 you have to
start with
> > > > 10/1/1999 and end with 2/28/2010 which is 10 years, 4 months.
There is one day
> > > > before October 1999 and 16 days into March 2010. That leaves a net
of 15 days,
> > > > right?
> > > >
> > > > Bill
> > > >
> > > > --- In MS_Access_Professio nals@yahoogroups .com
> > > <mailto:MS_Access_ Professionals% 40yahoogroups. com> , "John Viescas"
<JohnV@>
> > > > wrote:
> > > > >
> > > > > A.D.-
> > > > >
> > > > > I find there's a slight difference between your function and
mine:
> > > > >
> > > > > ?FN_GetDateDiffAsYM D(#9/29/1999# )
> > > > > 10 Years, 5 Months, 17 Days
> > > > >
> > > > > ?DateDiffString( #9/29/1999# , Date())
> > > > > 10 years, 5 months, 16 days.
> > > > > 62 years, 5 months, 16 days.
> > > > >
> > > > > I would argue that since September 29 occurs at the end of a
month and
> > > > February
> > > > > has only 28 days, the 10 years and 5 months is correct, but my
calculation
> > > of
> > > > 16
> > > > > days - we are 16 days in to March - is more valid than yours.
Comments?
> > > > >
> > > > > John Viescas, author
> > > > > Microsoft Office Access 2007 Inside Out
> > > > > Building Microsoft Access Applications
> > > > > Microsoft Office Access 2003 Inside Out
> > > > > SQL Queries for Mere Mortals
> > > > > http://www.viescas. com/
> > > > > (Paris, France)
> > > > >
> > > > >
> > > > > -----Original Message-----
> > > > > From: MS_Access_Professio nals@yahoogroups .com
> > > <mailto:MS_Access_ Professionals% 40yahoogroups. com>
> > > > > [mailto:MS_Access_Professio nals@yahoogroups .com
> > > <mailto:MS_Access_ Professionals% 40yahoogroups. com> ] On Behalf Of
A.D. Tejpal
> > > > > Sent: Tuesday, March 16, 2010 2:16 PM
> > > > > To: MS_Access_Professio nals@yahoogroups .com
> > > <mailto:MS_Access_ Professionals% 40yahoogroups. com>
> > > > > Subject: Re: [MS_AccessPros] Days calculations Problem
> > > > >
> > > > > John, Jim,
> > > > >
> > > > > Feasibility of a universal function for calculation of date
difference
> > > in
> > > > > terms of completed years, months and days has often featured in
various
> > > > > discussion groups. It is also referred as age calculation
function when
> > > second
> > > > > argument is optional and defaults to today's date.
> > > > >
> > > > > Over the years, different flavors of such a function have been
> > > attempted.
> > > > It
> > > > > has generally been felt that an optimum solution would be one
that could
> > > > handle
> > > > > various combinations of date arguments in such a manner as to
provide sample
> > > > > results in the following manner:
> > > > >
> > > > > (a) 28-Feb-2006 to 29-Feb-2008 = 2 Years
> > > > > (b) 28-Feb-2006 to 28-Feb-2008 = 2 Years
> > > > > (c) 29-Feb-2008 to 28-Feb-2010 = 2 Years
> > > > > (d) 28-Feb-2008 to 28-Feb-2010 = 2 Years
> > > > > (e) 28-Feb-2006 to 29-Mar-2008 = 2 Years, 29 Days
> > > > > (f) 30-Apr-2007 to 30-May-2009 = 2 Years, 30 Days
> > > > > (g) 30-Apr-2007 to 31-May-2009 = 2 Years, 1 Month
> > > > > (h) 31-Mar-2005 to 30-Mar-2007 = 1 Year, 11 Months, 30 Days
> > > > > (i) 31-Mar-2005 to 31-Mar-2007 = 2 Years
> > > > >
> > > > > Apparently, a universal solution, capable of delivering
consistent
> > > results
> > > > > in above manner, has so far remained elusive. Sample function
named
> > > > > Fn_GetDateDiffAsYMD () as given below, is an attempt to fulfill
this
> > > > requirement.
> > > > > The second argument is optional and if not supplied, it defaults
to today's
> > > > date
> > > > > - the function then serves as age calculator, with first
argument being date
> > > > of
> > > > > birth.
> > > > >
> > > > > It would be nice, if interested members of this group could
carry out
> > > > > corroborative tests and confirm whether anything needs further
attention.
> > > > >
> > > > > Best wishes,
> > > > > A.D. Tejpal
> > > > > ------------
> > > > >
> > > > > ' Sample function for calculating date difference
> > > > > ' as Years, Months, days
> > > > > '=========== ========= ========= ==
> > > > > Public Function Fn_GetDateDiffAsYMD ( _
> > > > > DtFrom As Date, _
> > > > > Optional DtUpto As Variant) As String
> > > > > ' Returns date difference as a string of
> > > > > ' Years, Months, Days
> > > > > ' If second argument is not supplied, it defaults
> > > > > ' to today's date - the function then serves as
> > > > > ' age calculator, with first argument being
> > > > > ' date of birth.
> > > > > Dim Dt1 As Date, Dt2 As Date
> > > > > Dim Dy1 As Long, Dy2 As Long
> > > > > Dim Yr As Long, Mn As Long, Dy As Long
> > > > >
> > > > > Dt1 = DtFrom
> > > > > If IsMissing(DtUpto) Then
> > > > > Dt2 = Date
> > > > > Else
> > > > > Dt2 = IIf(IsDate(DtUpto) , DtUpto, Date)
> > > > > End If
> > > > >
> > > > > Dy1 = Day(Dt1)
> > > > > Dy2 = Day(Dt2)
> > > > >
> > > > > ' Get number of completed months
> > > > > Mn = DateDiff("m" , Dt1, Dt2) - _
> > > > > IIf(Dy2 < Dy1, 1, 0) ' (A)
> > > > >
> > > > > If Day(Dt1 + 1) = 1 Then
> > > > > ' Dt1 is last day of the month
> > > > > If Day(Dt2 + 1) = 1 Then
> > > > > ' Dt2 is last day of the month
> > > > > Dy = 0
> > > > > ' Undo the subtraction at (A)
> > > > > Mn = Mn + IIf(Dy2 < Dy1, 1, 0)
> > > > > Else
> > > > > If Format(Dt2, "mmdd") = _
> > > > > Format(Dt1, "mmdd") Then
> > > > > Dy = 0
> > > > > Else
> > > > > Dy = Dy2
> > > > > Mn = Mn - IIf(Dy2 >= Dy1, 1, 0)
> > > > > End If
> > > > > End If
> > > > > Else
> > > > > ' Balance days in starting month
> > > > > Dy = Day(DateSerial( Year(Dt1) , _
> > > > > Month(Dt1) + 1, 0)) - Dy1
> > > > > If Day(Dt2 + 1) = 1 Then
> > > > > ' Dt2 is last day of the month
> > > > > If Dy2 = Dy1 Then
> > > > > Dy = 0
> > > > > End If
> > > > > ' Undo the subtraction at (A)
> > > > > Mn = Mn + IIf(Dy2 < Dy1, 1, 0)
> > > > > Else
> > > > > If Dy2 < Dy1 Then
> > > > > Dy = Dy + Dy2
> > > > > Else
> > > > > Dy = Dy2 - Dy1
> > > > > End If
> > > > > End If
> > > > > End If
> > > > >
> > > > > Yr = Mn \ 12
> > > > > Mn = Mn Mod 12
> > > > >
> > > > > Fn_GetDateDiffAsYMD = IIf(Yr > 0, Yr & _
> > > > > IIf(Yr > 1, " Years", " Year"), "") & _
> > > > > IIf(Yr > 0 And Mn > 0, ", ", "") & _
> > > > > IIf(Mn > 0, Mn & _
> > > > > IIf(Mn > 1, " Months", " Month"), "") & _
> > > > > IIf((Yr > 0 Or Mn > 0) And Dy > 0, _
> > > > > ", ", "") & IIf(Dy > 0, Dy & _
> > > > > IIf(Dy > 1, " Days", " Day"), "")
> > > > > End Function
> > > > > '=========== ========= ========= =====
> > > > >
> > > > > ----- Original Message -----
> > > > > From: John Viescas
> > > > > To: MS_Access_Professio nals@yahoogroups .com
> > > <mailto:MS_Access_ Professionals% 40yahoogroups. com>
> > > > > Sent: Tuesday, March 16, 2010 12:30
> > > > > Subject: RE: [MS_AccessPros] Days calculations Problem
> > > > >
> > > > >
> > > > > Jim-
> > > > >
> > > > > It's a bug. This works:
> > > > >
> > > > > Public Function DateDiffString( datFromDate As Date, datToDate As
Date) As
> > > > > String
> > > > > Dim intYears As Integer, intMonths As Integer, intDays As
Integer
> > > > > Dim intDayFrom As Integer
> > > > >
> > > > > ' Calculate the years
> > > > > intYears = DateDiff("yyyy" , datFromDate, datToDate)
> > > > > ' But adjust if the month of "from" is > month of "to"
> > > > > If Month(datFromDate) >= Month(datToDate) Then
> > > > > intYears = intYears - 1
> > > > > ' .. or if months are the same but from date > to date
> > > > > ElseIf Month(datFromDate) = Month(datToDate) And
Day(datFromDate) >
> > > > > Day(datToDate) Then
> > > > > intYears = intYears - 1
> > > > > End If
> > > > > ' Calculate Months
> > > > > intMonths = DateDiff("m" , datFromDate, datToDate) - (intYears *
12)
> > > > > ' Adjust if the day of "to" is > the day of "from"
> > > > > If Day(datFromDate) > Day(datToDate) Then
> > > > > intMonths = intMonths - 1
> > > > > End If
> > > > > ' Calculate Days - depends on the ending month
> > > > > Select Case Month(datToDate)
> > > > > ' January, February, April, June, August, September, November
> > > > > Case 1, 2, 4, 6, 8, 9, 11
> > > > > ' Previous month has full 31 days, so end day of previous is OK
> > > > > intDayFrom = Day(datFromDate)
> > > > > ' March
> > > > > Case 3
> > > > > ' February has 28 or 29 days, so adjust
> > > > > ' Check for leap year
> > > > > If Month(DateSerial( Year(datToDate) , 2, 29)) = 2 Then
> > > > > ' Leap year
> > > > > If Day(datFromDate) > 29 Then
> > > > > ' Use 29
> > > > > intDayFrom = 29
> > > > > Else
> > > > > ' Use actual date
> > > > > intDayFrom = Day(datFromDate)
> > > > > End If
> > > > > Else
> > > > > ' Not a leap year
> > > > > If Day(datFromDate) > 28 Then
> > > > > ' Use 29
> > > > > intDayFrom = 28
> > > > > Else
> > > > > ' Use actual date
> > > > > intDayFrom = Day(datFromDate)
> > > > > End If
> > > > > End If
> > > > > ' May, July, October, December
> > > > > Case Else
> > > > > If Day(datFromDate) > 30 Then
> > > > > intDayFrom = 30
> > > > > Else
> > > > > intDayFrom = Day(datFromDate)
> > > > > End If
> > > > > End Select
> > > > > ' Just do a straight subtract if "from" day <= "to" day
> > > > > If intDayFrom <= Day(datToDate) Then
> > > > > intDays = Day(datToDate) - intDayFrom
> > > > > Else
> > > > > ' Number of days using previous month of the "to" date
> > > > > intDays = datToDate - DateSerial(Year( datToDate) ,
Month(datToDate) -
> > > 1,
> > > > > intDayFrom)
> > > > > End If
> > > > > ' Assemble the answer
> > > > > DateDiffString = intYears & IIf(intYears = 1, " year, ", "
years, ") & _
> > > > > intMonths & IIf(intMonths = 1, " month, ", " months, ") & _
> > > > > intDays & IIf(intDays = 1, " day.", " days.")
> > > > > End Function
> > > > >
> > > > > John Viescas, author
> > > > > Microsoft Office Access 2007 Inside Out
> > > > > Building Microsoft Access Applications
> > > > > Microsoft Office Access 2003 Inside Out
> > > > > SQL Queries for Mere Mortals
> > > > > http://www.viescas. com/
> > > > > (Paris, France)
> > > > >
> > > > >
> > > > > -----Original Message-----
> > > > > From: MS_Access_Professio nals@yahoogroups .com
> > > <mailto:MS_Access_ Professionals% 40yahoogroups. com>
> > > > > [mailto:MS_Access_Professio nals@yahoogroups .com
> > > <mailto:MS_Access_ Professionals% 40yahoogroups. com> ] On Behalf Of
Jim Wagner
> > > > > Sent: Monday, March 15, 2010 11:31 PM
> > > > > To: MS_Access_Professio nals@yahoogroups .com
> > > <mailto:MS_Access_ Professionals% 40yahoogroups. com>
> > > > > Subject: Re: [MS_AccessPros] Days calculations Problem
> > > > >
> > > > > Ok, I thought that this was working. I just looked at my form
and I have
> > > > > 1/3/2006 as my start date and the results are
> > > > > 4 years, 2 months, 40 days. Is there a tweak that I need to make
to get it
> > > > > correct?
> > > > >
> > > > > Jim Wagner
> > > > > ____________ _________ _________ __
> > > > >
> > > > >
> > > > >
> > > > >
> > > > >
> > > > > ____________ _________ _________ __
> > > > > From: John Viescas <JohnV@>
> > > > > To: MS_Access_Professio nals@yahoogroups .com
> > > <mailto:MS_Access_ Professionals% 40yahoogroups. com>
> > > > > Sent: Tue, February 23, 2010 3:37:14 AM
> > > > > Subject: RE: [MS_AccessPros] Days calculations Problem
> > > > >
> > > > >
> > > > > Ahmed-
> > > > >
> > > > > Sure. Try this:
> > > > >
> > > > > Public Function CalcDays(varConfirm ation As Variant,
varResignation As
> > > > > Variant)
> > > > > As Integer
> > > > > Dim datDateFrom As Date, datDateTo As Date, datTest As Date
> > > > >
> > > > > ' First, make sure there's a date in the first argument
> > > > > If Not IsDate(varConfirmat ion) Then
> > > > > ' Nope - return -1 to indicate error
> > > > > CalcDays = -1
> > > > > Exit Function
> > > > > End If
> > > > > ' Use the year of the first argument to calculate the first
comparison
> > > date
> > > > > datTest = DateSerial(Year( varConfirmation) , 7, 1)
> > > > > ' If confirmation date is less than July 1, use it
> > > > > If varConfirmation < datTest Then
> > > > > datDateFrom = varConfirmation
> > > > > Else
> > > > > ' Otherwise, use July 1
> > > > > datDateFrom = datTest
> > > > > End If
> > > > > ' If the resignation date is not a null
> > > > > If Not IsNull(varResignati on) Then
> > > > > ' Use the resignation date
> > > > > datDateTo = varResignation
> > > > > Else
> > > > > ' Figure out end of current fiscal year
> > > > > ' If confirmation is less than July 1
> > > > > If varConfirmation < datTest Then
> > > > > ' Use June 30 of confirmation year
> > > > > datDateTo = DateSerial(Year( varConfirmation) , 6, 30)
> > > > > Else
> > > > > ' Use June 30 of next year
> > > > > datDateTo = DateSerial(Year( varConfirmation) + 1, 6, 30)
> > > > > End If
> > > > > End If
> > > > > ' Return the answer
> > > > > CalcDays = DateDiff("d" , datDateFrom, datDateTo)
> > > > > End Function
> > > > >
> > > > > John Viescas, author
> > > > > Microsoft Office Access 2007 Inside Out
> > > > > Building Microsoft Access Applications
> > > > > Microsoft Office Access 2003 Inside Out
> > > > > SQL Queries for Mere Mortals
> > > > > http://www.viescas. com/
> > > > > (Paris, France)
> > > > >
> > > > > -----Original Message-----
> > > > > From: MS_Access_Professio nals@yahoogroups .com
> > > > > [mailto:MS_Access_ Professio nals@yahoogroups .com] On Behalf Of
Ahmed
> > > Hashim
> > > > > Sent: Tuesday, February 23, 2010 11:00 AM
> > > > > To: MS_Access_Professio nals@yahoogroups .com
> > > > > Subject: Re: [MS_AccessPros] Days calculations Problem
> > > > >
> > > > > John-
> > > > >
> > > > > Not working... :( it calculates wrong number of days....If
possible can u
> > > > > please create a vba function that do the same...???
> > > > >
> > > > > On Tue, Feb 23, 2010 at 2:33 PM, John Viescas <JohnV@viescas.
com> wrote:
> > > > >
> > > > > >
> > > > > >
> > > > > > I probably missed a paren somewhere.
> > > > > >
> > > > > >
> > > > > > DateDiff("d" ,
> > > > > > IIf([DateOfConfirma tion] < DateSerial(Year( [DateOfConfirmat
ion]), 7,
> > > > 1),
> > > > > > [DateOfConfirmation ],
> > > > > > DateSerial(Year( [DateOfConfirmat ion]), 7, 1)),
> > > > > > IIf(Not IsNull([DateOfResig nation]), [DateOfResignation] ,
> > > > > > IIf([DateOfConfirma tion] < DateSerial(Year( [DateOfConfirmat
ion]), 7,
> > > > 1),
> > > > > > DateSerial(Year( [DateOfConfirmat ion]), 6, 30),
> > > > > > DateSerial(Year( [DateOfConfirmat ion])+1, 6, 30)))
> > > > > >
> > > > > > Yeah, I missed at least 5 close parens. Try the above.
> > > > > >
> > > > > >
> > > > > > John Viescas, author
> > > > > > Microsoft Office Access 2007 Inside Out
> > > > > > Building Microsoft Access Applications
> > > > > > Microsoft Office Access 2003 Inside Out
> > > > > > SQL Queries for Mere Mortals
> > > > > > http://www.viescas. com/
> > > > > > (Paris, France)
> > > > > >
> > > > > >
> > > > > > -----Original Message-----
> > > > > > From:
> > > > > MS_Access_Professio nals@yahoogroups .com<MS_Access_ Professi
> > > > onals%40yahoogro
> > > > > ups.co
> > > > > m>
> > > > > >
> > > > > [mailto:MS_Access_ Professio nals@yahoogroups
.com<MS_Access_ Professi
> > > > > onals%40yahoog
> > > > > roups.com>]
> > > > > > On Behalf Of Ahmed Hashim
> > > > > > Sent: Tuesday, February 23, 2010 10:03 AM
> > > > > > To:
> > > > > MS_Access_Professio nals@yahoogroups .com<MS_Access_ Professi
> > > > onals%40yahoogro
> > > > > ups.co
> > > > > m>
> > > > > > Subject: Re: [MS_AccessPros] Days calculations Problem
> > > > > >
> > > > > > John-
> > > > > >
> > > > > > I think there is some syntax error in solution...? ?
> > > > > >
> > > > > > On Tue, Feb 23, 2010 at 1:27 PM, johnvmvp
> > > > > <JohnV@viescas. com<JohnV%40viescas . com>>
> > > > > > wrote:
> > > > > >
> > > > > > >
> > > > > > >
> > > > > > > Ahmed-
> > > > > > >
> > > > > > > Perhaps like this:
> > > > > > >
> > > > > > > DateDiff("d" , IIf([DateOfConfirma tion] <
> > > > > > > DateSerial(Year( [DateOfConfirmat ion], 7, 1),
[DateOfConfirmation ],
> > > > > > > DateSerial(Year( [DateOfConfirmat ion], 7, 1)), IIf(Not
> > > > > > > IsNull([DateOfResig nation]), [DateOfResignation] ,
> > > > > > IIf([DateOfConfirma tion] <
> > > > > > > DateSerial(Year( [DateOfConfirmat ion], 7, 1),
> > > > > > > DateSerial(Year( [DateOfConfirmat ion], 6, 30),
> > > > > > > DateSerial(Year( [DateOfConfirmat ion]+1, 6, 30)))
> > > > > > >
> > > > > > > John Viescas, author
> > > > > > > Microsoft Office Access 2007 Inside Out
> > > > > > > Building Microsoft Access Applications
> > > > > > > Microsoft Office Access 2003 Inside Out
> > > > > > > SQL Queries for Mere Mortals
> > > > > > > http://www.viescas. com/
> > > > > > > (Paris, France)
> > > > > > >
> > > > > > >
> > > > > > > --- In
> > > > > >
> > > > > MS_Access_Professio nals@yahoogroups .com<MS_Access_ Professi
> > > > onals%40yahoogro
> > > > > ups.co
> > > > > m>
> > > > > > <MS_Access_Professi onals%40yahoogro ups.co
> > > > > > m>,
> > > > > >
> > > > > > > Ahmed Hashim <ahmedhashim1@ ...> wrote:
> > > > > > > >
> > > > > > > > Dear All Experts,
> > > > > > > >
> > > > > > > > After a long time I am in need of your suggestions.
> > > > > > > >
> > > > > > > > I have a table with following structure:
> > > > > > > >
> > > > > > > > Emp ID (Text)
> > > > > > > > Emp Name (Text)
> > > > > > > > Date of Confirmation (Date/Time)
> > > > > > > > Date of Resignation (Date/Time)
> > > > > > > >
> > > > > > > >
> > > > > > > > I need to calculate number of days between financial year
starting
> > > > from
> > > > > > > > 01-Jul-2007 to 30-Jun-2008. I tried to use DateDiff
Function but due
> > > > to
> > > > > > > > complexities of crietiras I don't get the desired results.
> > > Followings
> > > > > > are
> > > > > > > > the criterias:
> > > > > > > >
> > > > > > > >
> > > > > > > > 1.If Employee's Date of Confirmation is less then
01-Jul-2007 then
> > > > > > > > 01-Jul-2007 will be used as Start Date(Date1 of DateDiff
functions
> > > > > > > > parameter). Example: if employees date of confirmation is
> > > 01-Jan-2006
> > > > > > > then
> > > > > > > > Date1 should be 01-Jul-2007.
> > > > > > > >
> > > > > > > > 2. Date of resignation will need to be checked, if it is
not blank
> > > > > > means
> > > > > > > > employee resigned during the year, so Date2 need to be
Date of
> > > > > > > Resignation
> > > > > > > > of Employee, otherwise Date2 will be 30-06-2008.
> > > > > > > >
> > > > > > > > Is it possible to do it with DateDiff function in
Ms.Access Query,
> > > or
> > > > > > > there
> > > > > > > > is need to create VBA function hich I've tried and failed!
:(
> > > > > > > >
> > > > > > > > By the way I am using Ms.Access ver 2000 (still!)
> > > > > > > >
> > > > > > > > An early help in this regard will be highly appreciated.
..
> > > > > > > >
> > > > > > > > thanks in advance
> > > > > > > >
> > > > > > > > Ahmed
[Non-text portions of this message have been removed]
__._,_.___
Posted by: John Viescas <johnv@msn.com>
| Reply via web post | • | Reply to sender | • | Reply to group | • | Start a New Topic | • | Messages in this topic (46) |
.
__,_._,___
Tidak ada komentar:
Posting Komentar