month(now()) /* Output: 6 (when now() = June 24, 2022) /* Assume a property called Date with a current date of Jan 1, 2022 prop("Date").month() /* Output: 1 */
month() can be used with other functions such as dateSubtract to change the value of a date, like so:
/* Assume the value of now() is June 24, 2022 */ dateSubtract(now(), month(now()), "months") /* Output: December 24, 2022 */
The example database below determines each person’s next birthday based on their Birth Date, as well as the current date.
dateAdd( prop("Birth Date"), year( now() ) - year( prop("Birth Date") ) + if( month( prop("Birth Date") ) == month( now() ) and date( prop("Birth Date") ) >= date( now() ) or month( prop("Birth Date") ) > month( now() ), 0, 1 ), "years" )
Here’s the logic behind this formula:
- Start with the birth year.
- Add a number of years to it using dateAdd, determined by the year output of now minus the birth year itself (E.g. 2022 – 1991 = 31).
- Determine if the person’s birthday has already happened this year. If not, add one more year (since we’re determining their next birthday).
- The current month and the birth month are equal AND the current day of the month (determined with date) is equal to or later than the birth date’s day of the month
- OR the birth month is later than the current month
…then this indicates that the person’s birthday has NOT yet happened this year. Therefore, we add 0 to the count.
Otherwise, we add 1 to the count.