Use cases for Calculated Fields and Datediff function:

More information about all the parameters and options of DateDiff  function is available at http://msdn.microsoft.com/en-us/library/ms189794.aspx.

1. 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.

The function Datediff(year, [@field:DOB], GetUTCDate() is used to calculate age. To use this function to calculate at month and day granularity:

Datediff(month,[@field:DateLogged],GetUTCDate())/12

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

2. 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)

3. 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()))