Creating a customized CSV template or a customized report title, but aren't sure what formula you need  to output the correct data? Your help guide is here!

First, here are the Basics about how to use them.

Check out how specific formulas for:

BASICS

Formulas are always in the basic form of { type : input } where type is the field such as "expense", "report", "user", etc., and input is a feature describing 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. For example,{ 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 who submitted this 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.

__________________________________________________________________

EXPENSES

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. 

Amount

The amount of the expense.

{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.

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

{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.

{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.

{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.

{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.

{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.

{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.

{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.

{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.

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

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

{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.

Manual/Cash Expenses{expense:card} would output Cash assuming the expense was manually entered using either the website or the mobile app.
Bank 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.

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

Comment

The comment on the expense.

{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.

{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

You can modify the default formatting by removing, adding or reordering the date and time. Please note, dates are case insensitive! That is, mm is different than MM. 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  

Most Common Date Formulas

Formula              Will Display Data As
M/dd/yyyy        5/23/2015        
MMMM dd,yyyy  January 23, 2015        
dd MMM yyyy    23 Jan 2015          
yyyy/MM/dd      2015/01/23        
MMMM,yyyy        January, 2015        
MMM, yyyy        Jan, 2015          
yy/MM/dd          15/01/23          
dd/MM/yy          23/01/15        
yyyy                  2015 

 Posted 

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

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

Distance 

The details of a distance or mileage expense type.

Count{expense:distance:count} would output 100 assuming the expense was created for 100 mi/km driven.
Rate
{expense:distance:rate} would output 0.54 assuming the rate per mi/km is set to $0.54.
Unit
{expense:distance:unit} would output km assuming the expense was created in kilometers instead of miles.

 Currency/Exchange 

The currency of the expense.

{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.

{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. For Corporate plan users only.

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

MCC

The Merchant Category Code of the expense.

{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.

{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.

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

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

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

Reimbursable

The reimbursable flag of an expense.

{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.

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

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

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

Tag 

The tag of the expense.

{expense:tag} would output Henry at Example Co. assuming that is the expenses' tag. Tax The tax applied to the expense line item.

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

{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.

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

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

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

{expense:tax:code} would output the tax code that was set in the policy settings.

Multiple Tags (Corporate Plan Only)

Tags for companies that have multiple tags setup.

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

{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.

{report:submit:from:customfield1} outputs the custom field 1 information entered for the user who submitted the expense. If John Smith’s custom field 1 entry is 13456 this would output simply 13456.

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

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

{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.

{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.

A column on your spreadsheet that outputs all the GL codes associated with an expense. {report:submit:from:customfield1}-{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.

Custom Field Entries (Corporate Plan Only)

The custom field entries associated with the user or category of the expense.

{report:submit:from:customfield1} would output the custom field 1 entry associated with the user who submitted the expense. If John Smith’s custom field entry 1 is 100 this would output simply 100.

{expense:category:customfield2} would output the custom field 2 entry associated with the category of the expense. If the custom field 2 entry associated with Gym Memberships is EXP this would output simply EXP.

 __________________________________________________________________

REPORTS

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

Created

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

{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.

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

StartDate

The date of the earliest expense on the report.

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

EndDate

The date of the last expense on the report.

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

*StartDate and EndDate accept date optional formating. Please see Expense:Created for more information on date formats.

Automatic Reporting Dates

The start and end dates of the automatic reporting cycle.

{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.

{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.

Company Card Statement Reporting Dates 

{report:asrReporting:start} would output 2010-09-15 assuming that is the date the report was created.

{report:asrReporting:end} would output 2010-09-26 assuming that is the end date of the statement reporting cycle set in Domain Control > Company Cards > Statement Reporting.

Approve Date

The date the report was approved.

{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.

{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.

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

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

{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.

ACH Reimburse

The date the ACH transfer was submitted.

{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.

{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.

{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.

{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.

{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.

{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.

{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.

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

Date

The date that the report was submitted.

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

*Date accepts date optional format. Please see Expense:Created for more information on date formats.

From

The email address or custom field entry of the report submitter.

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

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

{report:submit:from:customfield1} would output the custom field entry for the user who submitted the report. If John Smith’s custom field 1 entry 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.

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

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

Title

The title of the report.

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

Report Field

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

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

Total 

The total cost of the expense report.

{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.

Currency

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

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

__________________________________________________________________ 

USER

Information about the current user or the report users.

Email

The email of the currently logged in Expensify user.

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

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

From

The email address or custom field entry of the report submitter.

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

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

{report:submit:from:customfield1} would output the custom field entry for the user who submitted the report. If John Smith’s custom field entry 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.

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

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

MATH

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

* — Multiplication {math: 3 * 4} output 12

/ — Division {math: 3 / 4 }output 0.75

+ — Addition {math: 3 + 4 }output 7

- — Subtraction {math: 3 - 4 }output -1

^ — Exponent {math: 3 ^ 4 } output 81

sqrt — The square root of a number. {sqrt:64} output 8

 __________________________________________________________________

SUBSTRING

The substring formula will output a subset of the string in question. The substring function is called by |substr::. It is important to remember that the count starts at 0 not 1.

{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.

{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.

{report:submit:from:customfield1} would output the custom field 1 entry of the user who submitted the report. If John Smith’s custom field 1 entry is 13456 this would output simply 13456.

{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.

{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.

{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.

{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.

A column on your spreadsheet that outputs all the GL codes associated with an expense. {report:submit:from:customfield1}-{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.

__________________________________________________________________ 

CUSTOM FIELDS

We recently changed the User ID and Payroll ID fields to appear as Custom Field 1 and Custom Field 2 within the people table in the policy settings. Please note that these are available at the corporate-level only.

{report:submit:from:customfield1} would output the custom field 1 entry associated with the user who submitted the report. If John Smith’s custom field entry 1 is 100 this would output simply 100.

{expense:category:customfield2} would output the custom field 2 entry associated with the category of the expense. If the custom field 2 entry associated with Gym Memberships is EXP this would output simply EXP.

Did this answer your question?