Advice needed Tricky Dates (1 Viewer)

Hello folks

I wonder if anyone can advise me as to how to address the following problem:

On a form I have to input a [StartDate] and an [EndDate]

The underlying query then calculates the total number of days between these dates [DaysWorked].

This works so far.

What I would like to do next is, from the number [DaysWorked] calculate the number of full years and display this in a box

and

Calculate the number of days in addition to a full year and display that in a different box.

Example:
[StartDate] Input = 12/11/2015
[EndDate] Input = 11/05/2019

[DaysWorked] Calculated = 1276
[FullYears] Calculated = 3
[DaysOver] Calculated = 181 (I think)

If I can calculate [FullYears] and [Days Over] and isolate them separately I can then use these for other calculations.

Is such a thing possible?

I know if it is, someone on here will know for sure!

Working with dates is never clean when you use more than one unit. Years don't have a definite amount of days, so you need to decide what constitutes a full year, program accordingly and accept imperfections.

How many full years from Feb 29 2000 to Feb 28 2001? How many days over?

My advice is to make a custom function to calculate your version of this using the built in DateDiff function (https://www.techonthenet.com/access/functions/date/datediff.php).

First you would determine total days between the two dates, put that in a variable. Then for years, divide that number by 365. For Days, use the Mod operator.

To understand this, you need to know about data in Access "DATE" format. A DATE variable or field is actually something called a TypeCast. A TypeCast is like saying "an alternate way to look at something." A character, for example, is really just a typecast of a byte integer and it applies USASCII encoding rules to represent text.

A DATE is a TypeCast of DOUBLE format. The internal representation is that your DATE variable is the number of days and fractions thereof between the expressed date and the Access "reference" date. According to the link, day 0 is actually 30 Dec 1899.

https://support.microsoft.com/en-us...nd-compare-date-time-data-in-microsoft-access

The routines internal to Access can convert dates into a DOUBLE that is the time difference from the reference date to the given date. In essence, it maps dates to a timeline using a linear function. This difference has the property that the difference between two date variables is the difference between two dates in days and fractions.

Therefore, computing the difference between two dates gives you the days in DOUBLE format (since dates are TypeCasts of DOUBLE). You are not worried about time of day, apparently, so this makes it simple. I'm going to also assume that you don't care TOO much about Leap Year issues. But I'll address that momentarily.


Now, the only wrinkle in this is knowing whether a Leap Year is involved. As it happens, Access automatically covers this problem since the timeline conversion correctly handles leap years. However, if you want to be SCRUPULOUSLY correct,....


Now you can adjust the Days Over to have one less day for each complete four-year period. It might be a bit more complex to worry about whether a Leap Year was involved for a sequence of less than four years but more than two years.

Wow 'The_Doc_Man'. What a response! Many thanks

Rob