Advice and answers from the Expensify Team


BASICS


  • Formulas are always in the basic form of "{ type : input }" where type is one of the below words and input is either another formula, or some text relevant to the type. 

Example: "{user:email}" would output the current user's email (i.e. your email right now).

  • The spacing in a formula is optional. The formula "{user:email}" is equivalent to "{ user : email }". Formulas are also case insensitive for the most part. That is to say "{ UsEr : emAIL }" is equivalent to "{ user : EMAIL }". Wherever this isn't the case is documented below.
  • Formulas can be intermixed with arbitrary text or even other formulas to form larger, more complex output. And formulas can even be nested within each other.

Example: "My email is {user:email}" would output "My email is bob@example.com" (assuming that was your email of course).

Example: "User whom submitted report is {report:submit:from} would output the user that created & submitted the report.

Example: "My email is {user:email|frontPart}" would output "My email is bob" (assuming that was your email of course). The function "|frontPart" can be used in any formula that calls an email address as its data point.

Example: "{math: {report:total:nosymbol} - {expense:amount:nosymbol} }" would output the difference of the total for a report and an expense on it. See the associated help sections for more information on the "report" and "expense" types.


Expense Formulas



The Expense type is used to get data specific to an item on a report. This will be different for each line in the exported report.

Expense accepts the following inputs:

Amount
The amount of the expense.

Example:"{expense:amount}" would output "$3.95" assuming the expense was for three dollars and ninety-five cents.

You may add an optional extra input that is either a three-letter currency code or “NONE” to denote the output's currency. The default if one isn't provided is "USD."

Example: "{expense:amount:isk}" would output "Íkr3.95" assuming the expense was for 3.95 Icelandic króna.

Example: "{expense:amount:nosymbol}" would output "3.95". Notice that there is no currency symbol in front of the expense amount because we designated "none".

To get the expense in its original currency you can use the following formula: "{expense:amount:originalcurrency}". This gives the amount of the expense in the currency in which it occurred.

Use "nosymbol" to get the expense in its original currency without the currency symbol: “{expense:amount:originalcurrency:nosymbol}

{expense:amount:negsign} displays negative expenses with a minus sign in front rather wrapped in parenthesis.

Example: "{expense:amount:negsign}" would output "-$3.95" assuming the expense was a negative expense for three dollars and ninety-five cents.

{expense:amount:unformatted} displays expense amounts without commas. This removes commas from expenses that have an amount of more than 1000.

Example: "{expense:amount:unformatted}" would output "$10000" assuming the expense was for ten thousand dollars.

{expense:debitamount} displays the amount of the expense if the expense is positive. Nothing will be displayed in this column if the expense is negative.

Example: "{expense:debitamount}" would output "$3.95" assuming the expense was for three dollars and ninety-five cents.

{expense:creditamount} displays the amount of the expense if the expense is negative. Nothing will be displayed in this column if the expense is positive.

Example: "{expense:creditamount}" would output "-$3.95" assuming the expense was for negative three dollars and ninety-five cents.

{expense:originalamount} is the amount of the expense imported from your bank or credit card feed. Corporate plan users only.

Example: "{expense:originalamount}" would output “$3.95” assuming the expense equated to $3.95 and you use US-based bank. You may add an optional extra input that is either a three-letter currency code or “NONE” to denote the output's currency.

The corresponding currency code for {expense:originalamount} is simply {expense:currency}.

For expenses imported via CDF/VCF feed only: 

{expense:purchaseamount} is the amount of the original purchase in the currency it was purchased in. Corporate plan users only.

Example: "{expense:purchaseamount}" would output "Irk 3.95" assuming the expense was for 3.95 Icelandic króna, no matter what currency your bank has translated it to.

Note: {expense:purchaseamount} is only available for users who import expenses using VCF/CDF feeds.

Billable

The billable flag of an expense.

Example: "{expense:billable}" would output "yes" assuming the expense is billable.

Billable is a boolean, and thus accepts an optional format input. Accepted formats are "string," "boolean," and "bit." The default if a format is not provided is "string."

Example: "{expense:billable:string}" would output "yes" if the expense is billable and "no" if it is not billable.

Example: "{expense:billable:boolean}" would output "true" if the expense is billable and "false" if it is not billable.

Example: "{expense:billable:bit}" would output "1" if the expense is billable and "0" if it is not billable.

Card
The name of the card the expense was created on.

Example:
Manual/Cash Expenses — "{expense:card}" would output "Cash" assuming the expense was manually entered using either the website or the mobile app.
Credit Card Expenses — "{expense:card}" would output "user@company.com - 1234" assuming the expense was imported from a credit card feed.
If you do not have access to the card that the expense was created on then 'Unknown' will be displayed.

Category
The category of the expense.

Example: "{expense:category}" would output "Office Supplies:Stationary" assuming that is the expenses' category.

Comment
The comment on the expense.

Example: "{expense:comment}" would output "Meeting with Bill from accounting" assuming that is the expenses' comment.

Created
The date the expense was originally created by the user.

Example: "{expense:created:yyyy-MM-dd HH:mm:ss}" would output “2010-09-15 12:00:00” assuming the expense was created on September 15th, 2010 at noon

Created is a date and thus accepts an optional format string. The default if one is not provided is “yyyy-MM-dd hh:mm:ss.” Note, dates are the one exception to formula case insensitivity. That is, “mm” is different than “MM”. Please see Expense:Created for more information.

To further customize a date formula, here is a list of abbreviations you can use.

Format                  Description                                                                               Example
s                            The seconds of the minute between 0-59.                               "0" to "59"
ss                          The seconds of the minute with leading zero, if required.        "00" to "59"
m                           The minute of the hour between 0-59.                                     "0" to "59"
mm                        The minute of the hour with leading zero, if required.              "00" to "59"
hh                          The hour of the day with leading zero if required.                    "01" to "12"
HH                         The hour of the day with leading zero, if required.                   "00" to "23"
dd                          The day of the month with leading zero, if required.                "01" to "31"
M                           The month of the year between 1-12.                                      "1" to "12"
MM                        The month of the year with leading zero, if required.               "01" to "12"
 yy                          Displays the year as a two-digit number.                                 "99" or "07"
 yyyy                      Displays the full four-digit year.                                             "1999" or "2007"
 
Most Common Date Formulas

Formula                  Will Display Data As
M/dd/yyyy               5/03/2015
MMMM dd,yyyy      January 23, 2015
dd MMM yyyy         23 Apr 2015
yyyy/MM/dd            2015/05/23
MMMM,yyyy           January, 2015
MMM, yyyy             November, 2015
yy/MM/dd               99/05/23        
yyyy                         2015
dd/MM/yy                23/05/99

Posted
The posted date of the expense (only available for commercial card feeds on Domain Control).

Example: "{expense:posted}"would output "2010-09-15 12:00:00" assuming the expense posted on September 15th, 2010 at noon.

Currency/Exchange
The currency of the expense.

Example: "{expense:currency}" would output "ISK" assuming the expense is in Icelandic króna.

The exchange rate used in converting the expense. The output is the expense currency (foreign currency) divided by your policy’s or your default output currency.

Example: "{expense:exchrate}" would output X/Y where X is the foreign currency and Y is your default currency.

For expenses imported via CDF/VCF feed only:

{expense:purchasecurrency} is the currency that the purchase was made in. <em>For Corporate plan users only.

Example: "{expense:purchasecurrency}" would output USD for a purchase that was made in US dollars.

MCC
The Merchant Category Code of the expense.

Example: "{expense:mcc}" would output "3351" assuming that is the expenses' MCC.

Note, we only have the MCC for expenses that are automatically imported or imported from an OFX/QFX file. For those we don't have an MCC for the output of the above example would be "" (an empty string).

Merchant
The merchant of the expense.

Example: "{expense:merchant}" would output "Peet's Coffee" assuming the expense is from a Peet's Coffee.

Receipt
Either a link to the receipt for the expense, or it's type.

Example: "{expense:receipt:type}" would output "eReceipt" if the receipt is an Expensify Guaranteed eReceipt.

Example: "{expense:receipt:url}" would output a link to the receipt image page that anyone could view.

Example: "{expense:receipt:url:direct}" would show the direct receipt image url for download.

Reimbursable
The reimbursable flag of an expense.

Example: "{expense:reimbursable}" would output "yes" assuming the expense is reimbursable.

Reimbursable is a boolean, and thus accepts an optional format input. Accepted formats are "string," "boolean," and "bit." The default if a format is not provided is "string."

Example: "{expense:reimbursable:string}" would output "yes" if the expense is reimbursable and "no" if it is not reimbursable.

Example: "{expense:reimbursable:boolean}" would output "true" if the expense is reimbursable and "false" if it is not reimbursable.

Example: "{expense:reimbursable:bit}" would output "1" if the expense is reimbursable and "0" if it is not reimbursable.

Tag
The tag of the expense.

Example: "{expense:tag}" would output "Henry at Example Co." assuming that is the expenses' tag.

Tax
The tax applied to the expense line item.

Example: "{expense:tax:field}" would output "VAT" assuming this is the name of the tax field.

Example: "{expense:tax:ratename}" would output the name of the tax rate that was used (ex: Standard). This will show "custom" if the chosen tax amount is manually entered and not chosen from the list of given options.

Example: "{expense:tax:amount}" would output "$2.00" assuming that is the amount of the tax on the expense.

Example: "{expense:tax:percentage}" would output "20%" assuming this is the amount of tax that was applied to the subtotal.

Example: "{expense:tax:net}" would output "$18.66" assuming this is the amount of the expense before tax was applied.

Multiple Tags (Corporate Plan Only)
Tags for companies that have multiple tags setup.

Example: {expense:tag:ntag-1} outputs the first tag the user chooses.

Example: {expense:tag:ntag-3} outputs the third tag the user chooses.

GL Code (Corporate Plan Only)
The GL Code associated with the user, category, or tag of the expense.

Example: “{report:submit:from:userid}” would output the User ID of the user who submitted the expense. If John Smith’s User ID is “13456” this would output simply “13456”.

Example: “{expense:category:glcode}” would output the GL code associated with the category of the expense. If the GL code for Meals is “45256” this would output simply “45256”.

Example: “{expense:tag:glcode}” would output the GL code associated with the tag of the expense. If the GL code for Client X is “08294” this would output simply “08294”.

Example: {expense:tag:ntag-3:glcode} outputs the GL code associated with the third tag the user chooses. This is only for companies that have multiple tags setup.

Example: “{field:Department|substr:6:5}”. You would use this when you have a GL code in a report field. In this example you have a report field titled “Department”. If the chosen field is “Sales 56789” this will output “56789”. For more information on this visit the Substring section.

Example: A column on your spreadsheet that outputs all the GL codes associated with an expense. “{report:submit:from:userid}-{expense:category:glcode}-{expense:tag:glcode}-{field:Department|substr:6:5}” outputs “13456-45256-08294-56789” for user: John Smith; category: Meals; tag: Client X; Department: Sales.

For more information please visit our GL mapping page.

Payroll Code (Corporate Plan Only)
The payroll code associated with the user or category of the expense.

Example: “{report:submit:from:payrollid}” would output the payroll code associated with the user who submitted the expense. If John Smith’s Payroll ID is “100” this would output simply “100”.

Example: "{expense:category:payrollcode}" would output the payroll code associated with the category of the expense. If the payroll code associated with Gym Memberships is “EXP” this would output simply “EXP”.

For more information please visit our Integration series.
_____________________________________________________________________________________

FIELD
The field type is used to access custom fields on a report (the fields at the top of the report). The input for a field formula is the name of a custom field.

Example: "{field:Employee ID}" would output "123456" assuming that is what the value of that field is on the report.
_____________________________________________________________________________________

MATH
The math type allows you to do arbitrary mathematical equations in your export.

Example: "{math: 3 * 4}" would output "12"</p>

* - Multiplication (3 * 4 = 12)

/ - Division (3 / 4 = 0.75

+ - Addition (3 + 4 = 7)

- - Subtraction (3 - 4 = -1)

^ - Exponent (3 ^ 4 = 81)
_____________________________________________________________________________________

REPORTS


The report type allows you to access information about the specific report.

Accepted inputs are:

Created
The date the expense report was originally created by the user.

Example: "{report:created}" would output “2010-09-15 12:00:00” assuming the expense was created on September 15th, 2010 at noon.

Created is a date and thus accepts an optional format string. The default if one is not provided is “yyyy-MM-dd hh:mm:ss.” Note, dates are the one exception to formula case insensitivity. That is, “mm” is different than “MM”. Please see Expense:Created for more information.

ID
The ID number of the report. This is an unique number per report and can be used to identify specific reports.

Example: "{report:id}" would output "88432" assuming that is the report's ID.

StartDate
The date of the earliest expense on the report.

Example: "{report:startdate}" would output "2010-09-15" assuming that is the date of the earliest expense on the report.

StartDate is a date and thus accepts an optional format. Please see Expense:Created for more information on date formats.

EndDate
The date of the last expense on the report.

Example: "{report:enddate}" would output "2010-09-26" assuming that is the date of the last expense on the report.

EndDate is a date and thus accepts an optional format. Please see Expense:Created for more information on date formats.

Automatic Reporting Dates
The start date of the automatic reporting cycle.

Example: "{report:autoReporting:start}" would output "2010-09-15" assuming that is the start date of the automatic reporting cycle, when the automatic reporting frequency is not set to daily.

The end date of the automatic reporting cycle.

Example: "{report:autoReporting:end}" would output "2010-09-26" assuming that is the end date of the automatic reporting cycle, when the automatic reporting frequency is not set to daily.

Approve Date
The date the report was approved.

Example:“{report:approve:date}” would output “2011-09-25” assuming that is the date the report was approved.

Approve Date is a date and thus accepts an optional format. Please see Expense:Created for more information on date formats.

Reimbursable
The reimbursable total amount of the report.

Example: "{report:reimbursable}" would output "$143.43" assuming the report's reimbursable total was 143.43 US Dollars.

Reimbursable is a currency and thus accepts an optional three character currency code or "NONE." Please see Expense:Amount for more information on currencies.

Reimburse Date
The date an expense report was reimbursed.

Example: "{report:achreimburse}" would output would output “2011-09-25” assuming that is the date the report was reimbursed via ACH Direct Deposit.

Example: "{report:paypalreimburse}" would output “2011-09-25” assuming that is the date the report was reimbursed via PayPal.

Example: "{report:manualreimburse}" would output “2011-09-25” assuming that is the date the report was marked as reimbursed.

Reimburse Date is a date and thus accepts an optional format. Please see Expense:Created for more information on date formats.

Currency
The currency to which all of the expenses on the report are being converted. The output currency of the report.

Example: "{report:currency}" would output "USD" assuming that the report total was calculated in US Dollars.

ACH Reimburse
The date the ACH transfer was submitted.

Example: "{report:achreimburse}" would output "2010-09-15" assuming that is the date of the ACH transfer.

ACHReimburse is a date and thus accepts an optional format. Please see Expense:Created for more information on date formats.

Manual Reimburse
The date the report was marked as reimbursed.

Example: "{report:manualreimburse}" would output "2010-09-15" assuming that is the date of the manual reimbursement.

ManualReimburse is a date and thus accepts an optional format. Please see Expense:Created for more information on date formats.

PayPal Reimburse

The date the report was reimbursed to the Paypal account.

Example: "{report:paypalreimburse}" would output "2010-09-15" assuming that is the date of the Paypal reimbursement.

PaypalReimburse is a date and thus accepts an optional format. Please see Expense:Created for more information on date formats.

Export Date
Today's date when the report is exported. This outputs the current date at the time of export.

Example: "{report:dateexported}" would output "2013-09-15" assuming that is the date at the time of export.

DateExported is a date and thus accepts an optional format. Please see Expense:Created for more information on date formats.

Expenses Count
The number of total expenses on the report of this specific expense.

Example: "{report:expensescount}" would output "10" assuming that there were 10 expenses on the given report for this expense.

Expense Number
The ordinal number of the expense on its expense report.

Example: "{report:expense:number}" would output "2" assuming that the given expense was the second expense on its report.

Policy Name
The name of the policy the report is under.

Example: "{report:policyname}" would output "Sales" assuming that the given report was under a policy named "Sales".

Status
The status of the report. This is the same status as it appears on the Reports page.

Example: "{report:status}" would output "Approved" assuming that the report has been approved and not yet reimbursed.


Submit
Information relating to the report's submission.

There are several sub-options available for Submit, to see more information about any of them just click on the name.:

Date
The date that the report was submitted.

Example: "{report:submit:MMM dd, yyyy}" would output "Sep 15, 1986" assuming that is the date the report was submitted.

Date is a date and thus accepts an optional format. Please see Expense:Created for more information on date formats.

From
The email address or UserID of the report submitter.

Example: "{report:submit:from}" would output "bob@example.com" assuming that is who submitted the report.

Example: "{report:submit:from|frontPart}" would output "bob" if bob@example.com submitted the report.

Example: “{report:submit:from:userid}” would output the User ID of the user who submitted the report. If John Smith’s User ID is “13456” this would output simply “13456”.  This is available only to Corporate Policy customers.

To
The email address of the last person who the report was submitted to.

Example: "{report:submit:to}" would output "alice@example.com" assuming that is who the report was most recently submitted to.

Example: "{report:submit:to|frontPart}" would output "alice" assuming that is who the report was most recently submitted to.


Title
The title of the report.
Example: "{report:title}" would output "Example Co Lunch Meeting" assuming that is the report's title.

Total
The total cost of the expense report.

Example: "{report:total}" would output "$325.34" assuming that is the report's total.

Total is a currency and thus accepts an optional three character currency code or "NONE." If you want to do any math operations on the report total, you should use {report:total:nosymbol} to avoid an error. Please see Expense:Amount for more information on currencies.


SQRT
The square root of a number.
Example: "{sqrt:64}" would output "8"


USER


Information about the current user or the report users.

Email
The email of the currently logged in Expensify user.

Example: "{user:email}" would output "bob@example.com" assuming that is the currently logged in Expensify user's email.

Example: "{user:email|frontPart}" would output "bob" assuming that is the currently logged in Expensify user's email.

From
The email address or UserID of the report submitter.

Example: "{report:submit:from}" would output "joe@example.com" assuming that is who submitted the report.

Example: "{report:submit:from|frontPart}" would output "jane" if jane@example.com submitted the report.

Example: “{report:submit:from:userid}” would output the User ID of the user who submitted the report. If John Smith’s User ID is “13456” this would output simply “13456”.  This is available only to Corporate Policy customers.

To
The email address of the last person who the report was submitted to.

Example: "{report:submit:to}" would output "alice@example.com" assuming that is who the report was most recently submitted to.

Example: "{report:submit:to|frontPart}" would output "alice" assuming that is who the report was most recently submitted to.
_____________________________________________________________________________________

SUBSTRING
The substring formula will output a subset of the string in question. The substring function is called by "|substr:&lt;start&gt;:&lt;length&gt;". It is important to remember that the count starts at 0 not 1.

Example: “{expense:merchant|substr:0:4}” would output “Star” for a merchant named Starbucks. This is because we are telling it to start at position 0 and be of 4 character length.

Example: “{expense:merchant|substr:4:5}” would output “bucks” for a merchant named Starbucks. This is because we are telling it to start at position 4 and be of 5 character length.
_____________________________________________________________________________________

ACCOUNTING (CORPORATE PLAN ONLY)


Gl Code
The GL Code associated with the user, category, or tag of the expense.

Example: “{report:submit:from:userid}” would output the User ID of the user who submitted the report. If John Smith’s User ID is “13456” this would output simply “13456”.

Example: “{expense:category:glcode}” would output the GL code associated with the category of the expense. If the GL code for Meals is “45256” this would output simply “45256”.

Example: “{expense:tag:glcode}” would output the GL code associated with the tag of the expense. If the GL code for Client X is “08294” this would output simply “08294”.

Example: "{expense:tag:ntag-3:glcode}" would output the GL code associated with the third tag the user chooses. This is only for companies that have multiple tags setup.

Example: “{field:Department|substr:6:5}” — You would use this when you have a GL code in a report field. In this example you have a report field titled “Department”. If the chosen field is “Sales 56789” this will output “56789”. For more information on this visit the Substring section.

Example: A column on your spreadsheet that outputs all the GL codes associated with an expense. “{report:submit:from:userid}-{expense:category:glcode}-{expense:tag:glcode}-{field:Department|substr:6:5}” outputs “13456-45256-08294-56789” for user: John Smith; category: Meals; tag: Client X; Department: Sales.


PAYROLL CODE


The payroll code associated with the user or category of the expense.

Example: “{report:submit:from:payrollid}” would output the payroll code associated with the user who submitted the report. If John Smith’s Payroll ID is “100” this would output simply “100”.

Example: "{expense:category:payrollcode}" would output the payroll code associated with the category of the expense. If the payroll code associated with Gym Memberships is “EXP” this would output simply “EXP".

For more information please visit our Integration series.

Did this answer your question?