Thanks Arnel,i made a query out of the table and call the testESB() function.
see qryESB on how to call the function.
then i made 3 forms.
I took first date Oct 25, 2020 and End date Aug 24, 2022. In this case total days are 669 which is 100% correct. but when it converts in year month and days it displays 1 yr, 10 Mnths and 04 Days where as It should be 01 Year and 10 Months. So difference is 4 days at converting into yr, mnth & days. See below:i made a query out of the table and call the testESB() function.
see qryESB on how to call the function.
then i made 3 forms.
Oct-20 | 7 | Jan-21 | 31 | Jan-22 | 31 | ||
Nov-20 | 30 | Feb-21 | 28 | Feb-22 | 28 | ||
Dec-20 | 31 | Mar-21 | 31 | Mar-22 | 31 | ||
68 | Apr-21 | 30 | Apr-22 | 30 | |||
May-21 | 31 | May-22 | 31 | ||||
Jun-21 | 30 | Jun-22 | 30 | ||||
Jul-21 | 31 | Jul-22 | 31 | ||||
Aug-21 | 31 | Aug-22 | 24 | ||||
Sep-21 | 30 | 236 | |||||
Oct-21 | 31 | ||||||
Nov-21 | 30 | ||||||
Dec-21 | 31 | ||||||
365 | |||||||
Total Days | 669 |
Do While yrStart < yrEnd
If yrStart = yrOne Then
1. iDy = iDy + DatePart("y", DateSerial(yrStart, 12, 31)) - DatePart("y", dtStart) + 1
Else
'iDy = iDy + DatePart("y", DateSerial(yrStart, 12, 31))
2 iYr = iYr + 1
End If
yrStart = yrStart + 1
Loop
End If
3. iDy = iDy + DatePart("y", dtEnd)
Getting 638 days is trivial. That is either (Date2 - Date1) or that difference +1, depending on whether they count being hired and fired on the same day as 1 day of service. That part that is trickier is the 1 year 10 months 4 days response. There, you run into the question of "what is a month for business purposes?" Do they require counting the calendar months with 28, 29, 30, or 31 days for each month according to the calendar, or is a business month just 30 days long? Without knowing the business rule, it is hard to see where to go with this.
'
'change to last parameter to variant in case of Null value
Public Function testESB(ByVal dtStart As Date, dtEnd As Variant) As String
' arnelgp
' return the End of Service Benefit
' year, month and day
Dim yrStart As Integer, iYr As Integer, iMo As Integer, iDy As Integer
Dim yrEnd As Integer, yrOne As Integer, tmp As Integer, ret As String
Dim xDy As Integer, j As Integer
'assign today's date if dtEnd is Null
If IsNull(dtEnd) Then
dtEnd = Date
End If
yrStart = Year(dtStart)
yrOne = yrStart
yrEnd = Year(dtEnd)
If yrStart = yrEnd Then
iDy = DateDiff("d", dtStart, dtEnd) + 1
iMo = iDy \ 30
iDy = iDy - (iMo * 30)
GoTo check_point
Else
Do While yrStart < yrEnd
If yrStart = yrOne Then
iDy = iDy + DatePart("y", DateSerial(yrStart, 12, 31)) - DatePart("y", dtStart) + 1
Else
'iYr = iYr + 1
iDy = iDy + DatePart("y", DateSerial(yrStart, 12, 31))
End If
yrStart = yrStart + 1
Loop
End If
iDy = iDy + DatePart("y", dtEnd)
j = Month(dtStart) + 1
xDy = Day(DateSerial(yrOne, j, 0))
While iDy >= xDy
iMo = iMo + 1
iDy = iDy - xDy
j = j + 1
xDy = Day(DateSerial(yrOne, j, 0))
Wend
While iMo > 11
iYr = iYr + 1
iMo = iMo - 12
Wend
check_point:
If iYr <> 0 Then
ret = ret & iYr & " year" & IIf(iYr > 1, "s", "") & " "
End If
If iMo <> 0 Then
ret = ret & iMo & " month" & IIf(iMo > 1, "s", "") & " "
End If
If iDy <> 0 Then
ret = ret & iDy & " day" & IIf(iDy > 1, "s", "") & " "
End If
testESB = Trim$(ret)
End Function
@The_Doc_Man,
I understand it is messy. But just curious to know how this website :
https://www.timeanddate.com/date/durationresult.html?d1=25&m1=10&y1=2020&d2=24&m2=07&y2=2022&ti=on
Produces the below with is accepted by Saudi Government
From and including: Sunday, 25 October 2020
To and including: Sunday, 24 July 2022
Result: 638 days
It is 638 days from the start date to the end date, end date included.
Or 1 year, 9 months including the end date.
Or 21 months including the end date.
@arnelgp
Thanks for your usual support. The date diff is related to End of Service Benefit, so Saudi Government only accepts the calculations that equals to the above mentioned website.
The worksheet I pasted above just to demonstrate how it is calculating on my Access form. See the attached pic. pls and try using same dates in the given website that will display as I explained above to @The_Doc_Man.
Thanks and Regards,
But recently law has been amended and they require counting the calendar months with 28, 29, 30, or 31 days for each month according to the calendar.
ACRSTDT | ACRENDDT | totalDays | Years | Months | Days |
---|---|---|---|---|---|
25/11/2020 | 24/11/2021 | 365 | 1 | 0 | 0 |
29/10/2021 | 12/11/2021 | 15 | 0 | 0 | 15 |
04/11/2021 | 06/11/2021 | 3 | 0 | 0 | 3 |
10/02/2017 | 31/05/2022 | 1937 | 5 | 3 | 22 |
15/02/2020 | 12/02/2022 | 729 | 1 | 11 | 29 |
25/10/2020 | 24/08/2022 | 669 | 1 | 10 | 0 |
15/01/2015 | 09/11/2021 | 2491 | 6 | 9 | 26 |
25/10/2020 | 24/07/2022 | 638 | 1 | 9 | 0 |
01/02/2020 | 31/01/2021 | 366 | 1 | 0 | 0 |
01/02/2021 | 31/01/2022 | 365 | 1 | 0 | 0 |
SELECT
ACR.ACRSTDT
, ACR.ACRENDDT
, DateDiff("d",[acrstdt],[acrenddt]+1) AS totalDays
, DateDiff("yyyy",[acrstdt],[acrenddt]+1)+(Format([acrenddt]+1,"mmdd")<Format([acrstdt],"mmdd")) AS Years
, DateDiff("m",DateAdd("yyyy",[years],[acrstdt]),[acrenddt]+1)+(Day([acrenddt]+1)<Day([acrstdt]) And Month([acrenddt]+1)>=Month([acrstdt])) AS Months
, DateDiff("d",DateAdd("m",[months],DateAdd("yyyy",[years],[acrstdt])),[acrenddt]+1) AS Days
FROM ACR;
function getYMD(stDT as Date, endDT as Date) as string
dim years as integer
dim months as integer
dim days as integer
years=DateDiff("yyyy",[stdt],[enddt]+1)+(Format([enddt]+1,"mmdd")<Format([stdt],"mmdd"))
months=DateDiff("m",DateAdd("yyyy",[years],[stdt]),[enddt]+1)+(Day([enddt]+1)<Day([stdt]) And Month([enddt]+1)>=Month([stdt]))
days=DateDiff("d",DateAdd("m",[months],DateAdd("yyyy",[years],[stdt])),[enddt]+1)
getYMD="TotalDays: " & DateDiff("d",[acrstdt],[acrenddt]+1) & "; Years: " & years & "; Months: " & months & "; Days: " & days
end function
I will try your above code Arnel and will revert back to you..check and test the new code
Code:' 'change to last parameter to variant in case of Null value Public Function testESB(ByVal dtStart As Date, dtEnd As Variant) As String ' arnelgp ' return the End of Service Benefit ' year, month and day Dim yrStart As Integer, iYr As Integer, iMo As Integer, iDy As Integer Dim yrEnd As Integer, yrOne As Integer, tmp As Integer, ret As String Dim xDy As Integer, j As Integer 'assign today's date if dtEnd is Null If IsNull(dtEnd) Then dtEnd = Date End If yrStart = Year(dtStart) yrOne = yrStart yrEnd = Year(dtEnd) If yrStart = yrEnd Then iDy = DateDiff("d", dtStart, dtEnd) + 1 iMo = iDy \ 30 iDy = iDy - (iMo * 30) GoTo check_point Else Do While yrStart < yrEnd If yrStart = yrOne Then iDy = iDy + DatePart("y", DateSerial(yrStart, 12, 31)) - DatePart("y", dtStart) + 1 Else 'iYr = iYr + 1 iDy = iDy + DatePart("y", DateSerial(yrStart, 12, 31)) End If yrStart = yrStart + 1 Loop End If iDy = iDy + DatePart("y", dtEnd) j = Month(dtStart) + 1 xDy = Day(DateSerial(yrOne, j, 0)) While iDy >= xDy iMo = iMo + 1 iDy = iDy - xDy j = j + 1 xDy = Day(DateSerial(yrOne, j, 0)) Wend While iMo > 11 iYr = iYr + 1 iMo = iMo - 12 Wend check_point: If iYr <> 0 Then ret = ret & iYr & " year" & IIf(iYr > 1, "s", "") & " " End If If iMo <> 0 Then ret = ret & iMo & " month" & IIf(iMo > 1, "s", "") & " " End If If iDy <> 0 Then ret = ret & iDy & " day" & IIf(iDy > 1, "s", "") & " " End If testESB = Trim$(ret) End Function
//Edit: remove minus() function, i think we hit the nail!
Arnel, It Is Absolutely Brilliant .......check and test the new code
Code:' 'change to last parameter to variant in case of Null value Public Function testESB(ByVal dtStart As Date, dtEnd As Variant) As String ' arnelgp ' return the End of Service Benefit ' year, month and day Dim yrStart As Integer, iYr As Integer, iMo As Integer, iDy As Integer Dim yrEnd As Integer, yrOne As Integer, tmp As Integer, ret As String Dim xDy As Integer, j As Integer 'assign today's date if dtEnd is Null If IsNull(dtEnd) Then dtEnd = Date End If yrStart = Year(dtStart) yrOne = yrStart yrEnd = Year(dtEnd) If yrStart = yrEnd Then iDy = DateDiff("d", dtStart, dtEnd) + 1 iMo = iDy \ 30 iDy = iDy - (iMo * 30) GoTo check_point Else Do While yrStart < yrEnd If yrStart = yrOne Then iDy = iDy + DatePart("y", DateSerial(yrStart, 12, 31)) - DatePart("y", dtStart) + 1 Else 'iYr = iYr + 1 iDy = iDy + DatePart("y", DateSerial(yrStart, 12, 31)) End If yrStart = yrStart + 1 Loop End If iDy = iDy + DatePart("y", dtEnd) j = Month(dtStart) + 1 xDy = Day(DateSerial(yrOne, j, 0)) While iDy >= xDy iMo = iMo + 1 iDy = iDy - xDy j = j + 1 xDy = Day(DateSerial(yrOne, j, 0)) Wend While iMo > 11 iYr = iYr + 1 iMo = iMo - 12 Wend check_point: If iYr <> 0 Then ret = ret & iYr & " year" & IIf(iYr > 1, "s", "") & " " End If If iMo <> 0 Then ret = ret & iMo & " month" & IIf(iMo > 1, "s", "") & " " End If If iDy <> 0 Then ret = ret & iDy & " day" & IIf(iDy > 1, "s", "") & " " End If testESB = Trim$(ret) End Function
//Edit: remove minus() function, i think we hit the nail!