Solved Looking For Loop To Count Total Actual Days From 2 Dates (1 Viewer)

CJ_London

Super Moderator
Staff member
Local time
Today, 10:17
Joined
Feb 19, 2013
Messages
16,553
The function has parameters- You don’t appear to populating them
 

Ashfaque

Student
Local time
Today, 15:47
Joined
Sep 6, 2004
Messages
894
The parameter value are already on form and instead of DtStart and DtEnd, I replaced with
CExpDateOfJoining, LastWorkingDay and then triggering to executed the function.
 

CJ_London

Super Moderator
Staff member
Local time
Today, 10:17
Joined
Feb 19, 2013
Messages
16,553
So you are not using the code as supplied but adapted it since the code as supplied has parameters
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 18:17
Joined
May 7, 2009
Messages
19,169
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.
 

Attachments

  • database1.accdb
    636 KB · Views: 122

Pat Hartman

Super Moderator
Staff member
Local time
Today, 06:17
Joined
Feb 19, 2002
Messages
42,970
Formatting is for humans. If the field is defined as a date datatype, do NOT format it before doing calculations. Formatting turns the field into a string. Once you turn a date into a string it acts like a string and not like a date. That is where the confusion between UK and US formats will bite you.
 

Ashfaque

Student
Local time
Today, 15:47
Joined
Sep 6, 2004
Messages
894
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.
Thanks Arnel,

You code is far closer. I am trying to set it in my db and reduce 2 days from total days while testing with diff dates. Hopefully I could manage it and once done I will come back

Many thanks....
 

Ashfaque

Student
Local time
Today, 15:47
Joined
Sep 6, 2004
Messages
894
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:

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​
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 18:17
Joined
May 7, 2009
Messages
19,169
if you will see the logic on the code, it is not counting for month after month:
Code:
    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)

part 1, will count the days from start_date up to dec 31, 2020, which is correct = 68 days (correct)
part 3, the portion Datepart("y", dtEnd), which will calculate the days from jan 2022 - aug 2022 = 236 (correct)

unlike on your worksheet, you are Counting days on each month, my code is just counting How many years,
on your sheet you have 365 days in jan 21-dec 21, but i don't count it as days, i count it as 1 whole year already.

so you only add part 1 and part 3 = 68 + 236 = 304 days

304 \ 30 = 10 months
304 - (40 months * 30) = 4 days

therefore my count is 1 year, 10 months and 4 days!
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 05:17
Joined
Feb 28, 2001
Messages
26,996
One of the problems associated with using DATEDIFF function is when the unit in question is non-uniform in length. For instance, if you take the years difference between two dates with Dec 31 of year X and Jan 1 of year X+1, you will get 1 year even though only 1 DAY has passed. Months can have similar issues. I haven't tried all cases, but I believe there is some rounding (up) when working with DATEDIFF and fractional intervals.

The only difference that is always correct is days - because that is simply (Date2 - Date1) truncated to integers. Trying to get exact counts of other units will be messy.
 

Ashfaque

Student
Local time
Today, 15:47
Joined
Sep 6, 2004
Messages
894
@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,
 

Attachments

  • ESB.jpg
    ESB.jpg
    223.2 KB · Views: 91

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 05:17
Joined
Feb 28, 2001
Messages
26,996
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.
 

Ashfaque

Student
Local time
Today, 15:47
Joined
Sep 6, 2004
Messages
894
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.

Business month of 30 days was the previous setting. 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. Therefore, my problem is to get exact yr month n days.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 18:17
Joined
May 7, 2009
Messages
19,169
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!
 
Last edited:

Gasman

Enthusiastic Amateur
Local time
Today, 10:17
Joined
Sep 21, 2011
Messages
14,038
Why do you keep changing the dates? :mad:
One minute it is Oct 25 to Aug 24
Then before you know it it is now Oct 25 to July 24???

@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,
 

plog

Banishment Pending
Local time
Today, 05:17
Joined
May 11, 2011
Messages
11,611
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.

That doesn't help. If in your system every start date was the first of a month, or if the end date was the end of the month then you can do what you want to do.

But you don't. You are allowing people to start in the middle of a month and end in the middle of the month. So now you have 2 partial months to deal with and that makes things messy.

February 15 to March 14 (counting both days) is sometimes 28 days and sometimes 29 days. How many months? How many days? Can you make a formal logical rule to explain it?

What month takes precedence? Do I use 31 days in a month for something that happens Mid February to Mid March? Or do I use 28/29 days?
 

CJ_London

Super Moderator
Staff member
Local time
Today, 10:17
Joined
Feb 19, 2013
Messages
16,553
I suggested a method in post #16 but seems to have been ignored - so this is my solution which seems to meet all the OP's requirements
Query43 Query43

ACRSTDTACRENDDTtotalDaysYearsMonthsDays
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​
note the last two entries - different number of days due to a leap year - but still 1 year.

This is the SQL
SQL:
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;

use your own table to supply the start and end dates

or if you want a function - briefly this - you will need to add in some error handling in the event of nulls etc

Code:
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
 

Gasman

Enthusiastic Amateur
Local time
Today, 10:17
Joined
Sep 21, 2011
Messages
14,038
Another tack might be to use that website to submit the dates and then scrape their result?
I know @arnelgp has done a fair bit of that logic?
 

Ashfaque

Student
Local time
Today, 15:47
Joined
Sep 6, 2004
Messages
894
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!
I will try your above code Arnel and will revert back to you..

Thanks a lot
 

Ashfaque

Student
Local time
Today, 15:47
Joined
Sep 6, 2004
Messages
894
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 .......

its R..o..c..k..i..n..g....(y)(y)(y)

I have added few lines (at the check point) to avoid null / zero value as per my requirements and it produces EXACT Yrs, Months & Days as desired.

I really appreciate all the members here who supported me on this forum. I believe counting calender wize days and converting them into exact yrs months and days are no doubt a difficult task to produce accurate result. But Arnel has did it.

Once again thanks all of you gents......

Regards,
 

Users who are viewing this thread

Top Bottom