Calculating the number of days, months or years between two dates is a common scenario in apps created with Calcapp. Excel provides the DATEDIF formula function for this purpose, but Calcapp does not currently support it.
(DATEDIF is a compatibility function Microsoft added to support Lotus 1-2-3, a competing spreadsheet which was principally popular in the 1980s. While it isn’t fully supported by Excel – it doesn’t appear in the Insert Function dialog box – it is still commonly used.)
In this post, we’ll look at a few popular ways that DATEDIF is used and determine how to achieve the exact same results with Calcapp, without DATEDIF.
All the formula examples assume that there are two date and time fields named Date1 and Date2.
Calculating the number of days between two dates
When DATEDIF is used with its third parameter set to “D”, the number of complete days between two dates is calculated.
Both Excel and Calcapp represent dates as so-called sequential serial numbers. The integer part of the number represents the number of days that have elapsed since December 31, 1899, and the fractional part represents the time of day. (Learn more here.)
As such, the number 10.75 represents the 10th of January, 1900, at 6 PM (18:00). (0.75 means that three quarters of a day have elapsed.)
Armed with this knowledge, determining the number of days between two dates is easy: we just subtract one number from the other:
If the two dates have time components, the formula above returns a fractional number, unlike DATEDIF. To more fully replicate the behavior of DATEDIF, which only counts whole days, we’ll need to remove the fractional component using the TRUNC function:
If Date1 represents a later date than Date2, we’ll get a negative number. There are two approaches to solving this issue that we’ll explore here. DATEDIF returns a #NUM! error, but our favored approach is to make the negative number a positive one using ABS:
To instead return a #N/A error (there is no way to manually return a #NUM! error), we can use the NA function:
Calculating the number of months between two dates
When DATEDIF is used with its third parameter set to “M”, the number of complete months between two dates is calculated.
To make work easier for us, we’ll fully follow the DATEDIF approach here, of requiring that Date2 represents a later date than Date1, and otherwise return a #N/A error.
To account for dates that fall on different years, we use the YEAR function to extract the different years, and multiply that number by 12 to get the number of months. We add that number to the number of months that differ between the two dates, a number we get from invoking the MONTH function on both dates and subtracting the Date1 number from the Date2 number.
Lastly, we need to ensure that only complete months are counted. If the dates fall on different days, we therefore subtract one from the calculated value.
Here’s the finished formula:
Calculating the number of years between two dates
When DATEDIF is used with its third parameter set to “Y”, the number of complete years between two dates is calculated.
The formula for performing the calculation is similar to the one for calculating the number of complete months:
DATEDIF also supports the “MD”, “YM” and “YD” units, which ignores various date components. Let us know if you’d like us to cover those variations as well!