Date Field Manipulation Examples

To display only the 4-digit year:

Use the date function: YEAR(Today) to return the 4-digit year (like 2009)

To display the beginning of the year:

Use the MakeDate function: MakeDate(1, 1, year(Today))

To display the last day of the year:

Use the MakeDate function: MakeDate(1, 0, year(Today) + 1)

To display yesterday’s date:

Use the date math equation: Today - 1.

To display tomorrow’s date:

Use the date math equation: Today + 1.

To display the days between dates:

Use the date math equation: <Date> - <Date>

(Note that putting the earlier date first would yield a negative number.)

To display the number of days between today and a future date:

Use the date math equation: <Date> - Today

To display the number of days between today and a past date:

Use the date math equation: Today - <Date>

To display the number of days a matter has been open:

Use the date math equation: Today - Opened

To display the number of days between when a matter was opened and closed:

Use the date math equation: Closed - Opened

To calculate start and end dates for the current week (starting at Monday):

dStart = (Today - DOW(Today)) + 2

dEnd = dStart + 6

To calculate start and end dates for the next week (starting at Monday):

dStart = (Today - DOW(Today)) + 9

dEnd = dStart + 6

To calculate start and end dates for the current 2 weeks (starting at Monday):

dStart = (Today - DOW(Today)) + 2

dEnd = dStart + 13

To calculate the first and last days of the current month:

dStart = Today - DAY(Today) +1

dEnd = MakeDate(MONTH(Today)+1, 0, YEAR(Today))

To calculate the first and last days of the next month:

dStart = MakeDate(MONTH(Today)+1, 1, YEAR(Today))

dEnd = MakeDate(MONTH(Today)+2, 0, YEAR(Today))