Calculate a Specific Past Date Accurately from a Calculated Future date, considering Leap Years

[jsallen17]

I am trying to calculate a date in the past relative to a future date that has been determined by a calculated field in a query. The past date needs to be calculated exactly 1 year (or 12 months) prior to the calculated future date, taking into account leap years on either side of the equation.


I apologize in advance if the following information is "too much" detail, but it seemed best to give the background I am dealing with to the professional that may be able to help me with the problem I am trying to solve. Many thanks in advance.


Here is a some background:


I have been working with Access databases for about 10 years now. I would consider myself a low-level, intermediate user, at best, when it comes to database administration. I am one of the people responsible for maintaining the database.


I am working with an Access 2010 database that has data and objects that were originally created in Access 2000. The original database has been "converted" recently to Access 2010, but much of the data and the applicable field settings, especially in the primary table used, have been retained and not altered. Most of the core objects in the database have also been retained as unaltered. Most things work well, with some modifications to the newer Access 2010 coding as needed.


In the primary table used in this database, dates are stored to track license term dates. The license term is standardly 1 year long, but the initial, first license term can be less than 1 year long. The primary fields used for tracking the license term dates are date fields as follows:


Effective Date: This is the date the license term begins


Expire Date: This is the date the license term ends


Examples:


Full, 1 year (12 month) long license term


Effective Date: 1/1/2017
Expire Date: 12/31/2017


Partial license term
Effective Date: 6/1/2017
Expire Date: 12/31/2017


We prepare renewal data for the next future licensing period via a query with numerous date fields. The primary date field used to calculate the future dates, is the Expire Date field.


Our calculated future dates were becoming incorrect and I suspected, and confirmed that the calculations were yielding incorrect future dates due to leap years not being accounted for in the calculations. The incorrect dates were always in a future leap year.


Here is how we have the future date calculation set up currently:


We call the future Effective Date the Begin Date and the future Expire Date the End Date. These fields are named with alias's and the calculated data is not stored. It is only used to populate a spreadsheet from the exported query datasheet.




Calculated fields currently in use:


Begin Date: [Expire Date]-365
End Date: [Expire Date]+365


These fields will calculate correctly for a common year, but not a leap year, so we have been changing the subtraction and addition intervals to things like: +366, -366, or even things like +365-365+1 to get the correct future dates. These changes need to be done manually and at the correct period before a leap year will be in affect during the next licensing period. This is not preferable, nor do I believe the smartest way to use Access. I believe that correctly calculated dates can be achieved every time with a properly set up calculated field.


After numerous hours of internet research, I finally found an example of how someone had solved a similar problem with an IIf statement in a calculated query field.


Found at this website https://bytes.com/topic/access/answe...iversary-dates


Credit to Stewart Ross


His solution to getting an future anniversary date, factoring in leap years:


DateSerial(Year(Date()) + IIf(Month([DOB])*100+Day([DOB])<=Month(Date())*100+Day(Date()),1,0), Month([DOB]),Day([DOB])) AS Anniversary



I modified this example using my field name, Expire Date, as follows:


End Date: DateSerial(Year([Expire Date])+IIf(Month([Expire Date])*100+Day([Expire Date])<=Month([Expire Date])*100+Day([Expire Date]),1,0),Month([Expire Date]), Day([Expire Date]))


This expression yields the correct future End Date every time for both common and leap years. Now I need to subtract exactly 1 year (or 12 months depending on how this needs to be considered) from the calculated End Date to get the correct Begin Date.


I have tried numerous calculations using variations of the above example, and DateAdd function with various intervals ("yyyy", "d", "m", "q") to subtract from the calculated End Date and can only get correct calculations if the Begin Date falls in a leap year. If the Begin Date falls in a common year, the calculation is off by 1 day, 1 day too short of a full year. Nothing else has worked and some variations have yielded incorrect future dates from the original calculated End Date.

Many thanks in advance.

jsallen17

[June7]

I am confused. First you show calculating BeginDate from the ExpireDate. Then you say you need to calculate BeginDate from the EndDate. Won't calculating 1 year back from EndDate just give the ExpireDate? That's what I observed in testing.

Maybe:

BeginDate: DateSerial(Year([Expire Date])-IIf(Month([Expire Date])*100-Day([Expire Date])>=Month([Expire Date])*100-Day([Expire Date]),1,0),Month([Expire Date]), Day([Expire Date]))

[jsallen17]

Hi June7.

Thanks for the quick reply. I really appreciate it.

By the way, I missed one of the posting criteria of noting if my question was asked on another website or platform. I also asked Stewart Ross, via the Bytes website, as a reply to his original post. Considering his original post I found was from December of 2009, I was unsure if he or anyone would respond to my post on that thread. So, I also took a chance on this forum.

I tried your suggestion and did not get the result I need. I had also tried variations of the expression you proposed based upon the calculated field I did get to work correctly. Basically, I reasoned that I could just subtract, or reverse the expression to get the correct new Begin Date based upon the pre-calculated End Date field. I did not get the correct date using the variations. I always got a wrong date; usually 1 day short of the actual date I needed or the expression would add or subtract an additional year.

I'll try and simplify what I am attempting to do.

Starting with a current Effective (beginning) Date and the Expire (ending) Date of a license term year, I am attempting to calculate the next correct, future Effective and Expire dates for the next license term year. The licenses in this scenario are issued on an annual basis. An annual term is 1 year, or 365 days, or 12 months long, depending on how it is perceived, or calculated in Access terms. The initial license term can be shorter than 1 year, but all subsequent license terms will be a full 1 year.

The potential for a partial, initial license term is one of the complications I am trying to work around. All licenses I am working with are issued by an issuing body for the license term year they choose, so multiple Effective Dates and Expiration Dates apply across all of the licenses issued, but each issuing body uses only one expiration date for it's licenses.

We also apply the concept that 12:00 am is the beginning of new day.

Here is an example of a license term year for an issuing body:

Effective Date: 1/1/2019
Expire Date: 12/31/2019

This license takes effect at 12:00 am on 1/1/2019 and ends at 11:59 pm, or so, on 12/31/2019, yielding a 365 day license term year.

The next correct license term would be:

Effective Date: 1/1/2020
Expire Date: 12/31/2020

Using your proposed expression I would get the following result for the next Effective Date as the alias field name of BeginDate:

Effective Date: 1/1/2019
Expire Date: 12/31/2019

BeginDate: DateSerial(Year([Expire Date])-IIf(Month([Expire Date])*100-Day([Expire Date])>=Month([Expire Date])*100-Day([Expire Date]),1,0),Month([Expire Date]), Day([Expire Date]))

Result was that BeginDate is: 12/31/2018
The correct result needs to be: 1/1/2020

I got the same wrong result even when I used >=Month or <=Month, in the middle of the expression or when using variations to the +/-Day parts. Basically this expression ends up subtracting 1 year from the original Expire Date instead of adding 1 year. I tried this expression, and again variations of it, in a query calculated field and a table calculated field and got the same incorrect result each time.

I have been trying to base all calculations for the upcoming next term on the original Expire Date field because the expiration date for a given license term year will always be the same, but the initial effective date, for a new license, might be on any day of the term year before the expiration date.

Ironically, I can get the correct next term dates by using a work around of incorrect (at least as the calculation appears to be) and correct calculations. I don't particularly like this method, but I did get it to work today.

Current license term dates to sample:

Effective Date: 1/1/2019
Expire Date: 12/31/19

Next correct term dates for the next license year:

Effective Date: 1/1/2020
Expire Date: 12/31/2020

Using the following calculated fields:

Begin Date: [End Date]-364
End Date: DateSerial(Year([Expire Date])+IIf(Month([Expire Date])*100+Day([Expire Date])<=Month([Expire Date])*100+Day([Expire Date]),1,0),Month([Expire Date]), Day([Expire Date]))

Results:

Begin Date: 1/1/2020
End Date: 12/31/2020

2020 is a leap year in this example, but I also got the correct Begin Date for 2019 which is not a leap year.

I discovered this by creating a table with numerous calculated date fields to determine which fields calculated the next license term dates properly, based upon the original Effective Date and the original Expire Date. I sampled 20 record entries covering years from 2012 through 2025, to include numerous common and leap years, using various original Effective and Expire dates.

I did have issues if a license began on 3/1/2018, the Effective Date, for example because the internal date coding in Access would not let me enter 2/29/2019 as the anticipated Expire Date because 2019 is a common year with only 28 days in February. If I used the available date of 2/28/2019, Access lets me enter the date. This appears to be incorrect visually, but in fact the license term of 3/1/2018 through 2/28/2019 is 365 days, if the last date is included in the calculation. Fortunately, there are not any licenses in the mix I am currently working with that have this type of term year.

I prefer to have the next Effective Date (Begin Date) calculated correctly for all upcoming common and leap years, based upon the calculation from the End Date (next Expire Date). This may be too complicated to achieve without some other, more intricate process in Access. I am trying to keep the calculation process as simple as possible and accurate every time without needing to fiddle with the calculation each time it is needed, which is on a monthly basis.

I also tried various methods to achieve the desired result in an Excel workbook because the calculated license term data is exported to an Excel workbook and then Word, merge documents are associated with the workbook. Those attempts proved to be cumbersome and required a lot of manual manipulation to get the correct results.

Until someone more well versed than me, such as yourself, in using Access suggests something better, I may just move forward with the work around I discovered today. I would still appreciate any input on this problem.

Thanks again June7.

jsallen17

[orange]

I'm not sure if this is helpful, but you might want to consider it as part of an approach.
If you knew that the year involved in the proposed term was a leap year you may be able to adjust your dates accordingly.
Here's a function to determine if the year part of a given date is a leap year.

' ----------------------------------------------------------------
' Procedure Name: IsLeapYear
' Purpose: Determine if year in supplied date is a leap year
' Procedure Kind: Function
' Procedure Access: Public
' Parameter vDate (Date):
' Return Type: Boolean
' Author: Jack
' Date: 24-Feb-19
' ----------------------------------------------------------------
Function IsLeapYear(vDate As Date) As Boolean
'  Is the supplied year divisible by 100.
'  If it is, then it is a leap year if and only if it is also divisible by 400
'  else it is not a leap year

'  If the year is not divisible by 100,
' then it is a leap year -if and only if -it is divisible by 4
    
    Dim Yr As Integer
    Yr = year(vDate)
    If Yr Mod 4 = 0 Or (Yr Mod 400 = 0 And Yr Mod 100 = 0) Then
        IsLeapYear = True
    Else
        IsLeapYear = False
    End If

End Function

Good luck with your project.