Creating Formulas with Dates

Date functions allow you to convert numbers to dates, which you can then format to display as appropriate, and to convert dates to numbers.

Calculations Using Date and Date/Time Fields

Examples of calculations using date or date/time fields include the following:

Current date function

To have the current day's date appear on a report, use the current date function. For example, to display today’s date in your report, you would write this formula:

Code Snippet

Switch to dark mode

Switch to dark mode 
1
CurrentDate

Addition and subtraction

You can add days to date fields by adding a number. For example, if you know that an order is shipped five days after the order was placed, you would find the expected ship date with this formula:

Code Snippet

Switch to dark mode

Switch to dark mode 
1
 + 5

If was January 10, then this formula would return January 15.

You can also subtract days from date fields by subtracting a number. For example, if you want to find the number of days it took to ship an order, if the order date was five days before the ship date, you would use the formula:

Code Snippet

Switch to dark mode

Switch to dark mode 
1
 – 5

If was January 10, then this formula would return January 5.

The most useful application of these concepts is finding the difference in days between two date fields. For example, if you want to find the number of days it took to ship an order, you would use this formula:

Code Snippet

Switch to dark mode

Switch to dark mode

If was January 20 and was January 10, then this formula would return 10 days.

Year, month, and day functions

At times, you must extract the year, month, or day portions from a date field. To accomplish this task, you would use the Year ( ), Month ( ), or Day ( ) function. For example, if you were looking for monthly trends in your ordering cycle, you could use the Month ( ) function to extract the month portion of your order date fields using this formula: