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!