Formulas can perform several useful calculations on dates. For example, you can calculate the number of days between two dates, or you can add or subtract a certain number of days to a date. You can also convert a date to text using a wide variety of formats.
Usually we think of a date in terms of years, months, and days. Formulas, however, treat dates as a certain number of days—specifically, the number of days between that date and January 1, 4713 B.C., adjusted for the Gregorian calendar correction in October 1582. (The date 4713 B.C. is chosen for obscure astronomical reasons). For example, to a formula, the date August 7, 1991 is day number 2,448,476.
Fortunately you should never have to worry about numbers like 2,448,476. The formula will automatically convert a date field into the number of days, perform the calculation, and then convert back into a regular date again.
Since formulas handle dates as numbers, you can use any numeric operator or function to manipulate dates. However it doesn’t make much sense to take the square root of a date (although Panorama will let you). There are really only two numeric operations that make sense on dates—subtracting two dates to find the number of days in between and adding or subtracting a number of days to a date.
To calculate the number of days between two dates, just subtract one from the other. For example, the formula
«Ship Date»-«Order Date»
will calculate the number of days required to process an order.
To calculate an offset from a given date, just add the number to the date. For example the formula
«Ship Date»+30
calculates the normal due date 30 days after the ship date.
The today() function returns the number corresponding to today’s date, allowing you to use today’s date in a formula. For example, to calculate the age of an invoice use a formula like this.
today()-«Ship Date»
To calculate the due date for a library book, use the formula like this.
today()+14
This formula assumes that books are checked out for two weeks.
These functions allow you to convert a date into text, or text into a date. You should only use these functions if you want to store the result of a date calculation in a text field instead of a date field, or if you want to access a date that has been stored as text.
Note: Remember, formulas handle dates as numbers, so these functions actually convert numbers into text and vice versa. It’s up to you to make sure that these numbers actually represent the correct dates.
completedatestr(number) Convert a date to text, including the day of the week (for example Sunday, April 20th, 2003).
date(text) This function converts a text string in a date format into the number representing that date. Use this function to include a constant date in your formula, for example
date("12/9/2019")
You should also use this function to access dates that have been stored in text fields (but we recommond avoiding storing dates in text fields whenever possible, use Date fields instead, see Data Types.).
Several formats are supported, including mm/dd/yy, mm/dd/yyyy, Month dd, yyyy, and Mon dd, yyyy. (If you are in a country where the month and date are flipped, the *date(* function will accept dd/mm/yy and dd/mm/yyyy instead of the corresponding formats listed above.) Dates in the current week can be represented by the name of the day, for instance Tuesday or Fri. Dates in the previous or upcoming week can be represented by adding the words last or next, for example last friday or next wed.
datepattern(number,pattern) This function converts a number representing a date into a formatted text string. The pattern parameter is an output pattern telling the function how to format the date. For more information on date output patterns, see Date Patterns. Use the datepattern( function to store a date in a text field, or to display a formatted date in a Text Display Object. For example, the formula:
datepattern(«Ship Date»,"Month ddnth, yyyy")
can be used to display the date an order was shipped in the format May 12th, 2023.
datestr(number) Convert a date to text using format mm/dd/yy (for example 4/20/23).
daystr(number) Convert a date to the day of the week (for example Sunday).
eurodatestr(number) Convert a date to text in European format (for example 20-APR-2018).
exportcell(field) This function takes any database field and converts it to text, using the appropriate pattern if one has been defined in the design sheet. Field is the name of the field to be converted to text.
The function always returns a text type data item. The power of the exportcell( function is that it does not require you to know what type of data you are exporting. It simply takes whatever kind of data is in the field (text, number, date, whatever) and converts it into text.
longdatestr(number) Convert a date to text with format Month ddnth, yyyy (for example April 20th, 2020).
naturaldate(date) This function converts a date to text in a natural format similar to how people would refer to the date, for example Today, Tue, Apr 4. If the date is more than 180 days in the past or is in the future the pattern mm/dd/yy is used. This is similar to how the Apple Finder displays dates.
These functions perform various calculations and conversions on date values. Unless specified otherwise the date is always processed as a numeric value
datevalue(year,month,day) This function converts three integers into a date. The three integers are the year, month and date. This function provides a way to create a date that is independent of the system date settings (the date( function, which can also create dates, will produce different values in different countries depending on the date formats used in those countries).
dayofweek(date) This function computes the day of the week (0–6) of a date, with Sunday being 0, Monday 1, etc. The function returns a number from 0 to 6. The days of the week are:
0 Sunday
1 Monday
2 Tuesday
3 Wednesday
4 Thursday
5 Friday
6 Saturday
The procedure below uses the dayofweek( function to select all weekday records (monday through friday).
select dayofweek(Date)≥1 and dayofweek(Date)≤5
dayvalue(date) This function extracts the day of the month from a date as a numeric value (1 to 31).
firstdayofweek( This function returns the user’s preference for the first day of the week. It will return 0 for Sunday, 1 for Monday, etc. In the United States this value is usually zero (Sunday) as shown above, but this preference can be changed in the System Preferences Language & Region panel.
month1st(date) This function computes the first day of a month. For example, if the date passed to this function is October 18, 1997, this function will return the date October 1, 1997. The date is returned as a number.
The example procedure below uses this function to select the orders placed this month, then displays the count.
select OrderDate≥month1st(today()) and
OrderDate<month1st(today())+monthlength(today())
message str(info("selected"))+" orders this month"
monthlength(date) This function computes the length (number of days) of a month. For example, if the date passed to this function is October 18, 1997, this function will return 31, the number of days in October. This function knows about leap years and adjusts the length of February accordingly.
The example procedure below uses this function to select the orders placed this month, then displays the count.
select OrderDate≥month1st(today()) and
OrderDate<month1st(today())+monthlength(today())
message str(info("selected"))+" orders this month"
monthmath(date,offset) This function takes a date and computes another date that is one or months before or after the original date. Date is a number representing the original date. Offset is the number of months that you want to add or subtract to the original date. Use a positive number to move forward in time, a negative number to go backwards. For example, if you offset the date May 12, 1997 by 2 (two months forward) the result is July 12, 1997. If you offset the same original date by -2 (two months backward) the result is March 12, 1997.
If the new date does not exist because a month does not have enough days in it, the monthmath( function will pick the last day of the month. For example, if you offset March 31 by 1 month the result is April 30. If the new month lands in February the function knows about leap years and adjusts accordingly. This example calculates a renewal date exactly one year from today.
monthmath(today(),12)
monthvalue(date) This function extracts the month from a date as a numeric value (1 to 12).
quarter1st(date) This function computes the first day of a quarter. For example, if the date passed to this function is August 18, 1997, this function will return the date July 1, 1997. The date is returned as a number.
quartervalue(date) This function extracts the quarter within a year from a date as a numeric value (1 to 4).
today()This function returns today’s date (assuming, of course, that your computer clock has been set correctly).
week1st(date)This function computes the first day of a week (usually Sunday). For example, if the date passed to this function is July 12, 1995 (a Wednesday), this function will return the date July 9, 1997 (a Sunday). The date is returned as a number. Note: In the United States the first day of the week is usually Sunday, but this preference can be changed in the System Preferences Language & Region panel. You can check what the actual first day of the week is with the firstdayofweek( function.
weekvalue(date) This function extracts the week from a date as a numeric value (this is the number of weeks since the start of the year, 1 to 52).
year1st(date) This function computes the first day of a year. For example, if the date passed to this function is July 12, 1995, this function will return the date January 1, 1995. The date is returned as a number. The example below calculates the number of days remaining in the current year.
yearfirst(year1st(today())+366)-today()
yearvalue(date) This function extracts the year from a date as a numeric value.
See Also
History
| Version | Status | Notes |
| 10.0 | No Change | Carried over from Panorama 6.0 |