Calculated Fields and Datediff Function

Use cases for Calculated Fields and Datediff function:

More information about all the parameters and options of DateDiff function is available in this article.

HOW TO SUBTRACT TWO DATES TO CALCULATE DATE DIFFERENCE, LIKE THE AGE OF A PERSON?

With Caspio Calculated Fields, you can find the difference between two dates and calculate, for example, the age of a person to year, month, or day granularity.

Date_of_Birth in the [@field:Date_of_Birth] should be replaced with the used field name.

FLOOR(DATEDIFF(YEAR,[@field:Date_of_Birth],GetDate()) –
(CASE WHEN DATEADD(YEAR,DATEDIFF(YEAR,[@field:Date_of_Birth],GetDate()),
[@field:Date_of_Birth]) > GetDate()
THEN 1
ELSE 0
END ))

HOW TO DETERMINE THE NUMBER OF YEARS BETWEEN TWO DATES?

Use Calculated Fields and Datediff function to determine the number of years between two dates. Here are some examples:

Number of years between two dates: Datediff (year, datefield1, datefield2)

Number of months between two dates: Datediff (month, datefield1, datefield2)

Number of days between two dates: Datediff( day, datefield1, datefield2)

HOW TO CALCULATE DATE DIFFERENCE SUCH AS DAYS BETWEEN WHEN A RECORD WAS SUBMITTED AND NOW?

Datediff (day, [@field:DateLogged], GetUTCDate())

DateLogged should be replaced with the name of your field where record submission dates are stamped.

To adjust the current date and time to your time zone see the example below which adjusts the time to Pacific which is 8 hours behind UTC.

Datediff (day, [@field:DateLogged], Dateadd(hour,-8,GetUTCDate()))