Export format - is there an expense date custom formula?
Hello,
I am creating an export format to check for any expenses submitted after 1/1/18 that have a date in 2017 and are above a threshold amount. So I want to include a column for the date at the expense level, not the report level. However, a custom formula for the date on the expense cannot be found here: https://docs.expensify.com/setup-for-admins-and-accountants/advanced-configuration-for-managers-and-accountants/custom-formulas
My guesses of {expense:date} and {expense:date:M/dd/yyyy} do not work.
The closest thing to what I'm looking for is to use the report start date {report:startdate}, which would at least tell me if any of the expenses on the report are from 2017. However, this still requires more effort than needed, as it would require me to bounce back & forth from the export format to specific the Expensify reports to scrutinize each the dates of the actual expenses. This would be SO MUCH EASIER if I could use the date of the expense, as then I could sort by date and amount and be done.
Any help is most appreciated!
Thank you,
Mollie
Best Answer
-
Sheena Trepanier Expensify Team, Approved! Accountant, Expensify Student Ambassador Posts: 1,362 Expensify TeamHi @mollie, happy to help with this! You should be able to find the formula for created date under the Created section. I've copied the information for you below though and I hope this helps!
Expense created date
{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. For the full list of abbreviations please visit the help article here, and look under the Created section.
The most common date formulas are below:
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 = 2015You're example of {expense:date:M/dd/yyyy} should be {expense:created:M/dd/yyyy}. I hope this helps!
Answers
-
Sheena - thank you so much! I had avoided using the date created field out of fear it wasn't the date on the expense, but the date the expense was created (often folks create expenses late). But I used this field in my export format as suggested, and it appears to be exactly what I needed. Thank you!!
-
Sheena Trepanier Expensify Team, Approved! Accountant, Expensify Student Ambassador Posts: 1,362 Expensify Team
That is awesome to hear @mollie, I'm glad that field did the trick.
-
Sheena,
How can I get todays date into the Posting Date and Document Date? We want to post the entire report(for each employee) as one AP invoice, so the expense date will not help.
Regards
Ram Bu
-
Hey @Ram_Reddy
I'm not super clear what you mean by document date, can you give me an example? It might be easier to live chat this through with us on the web, mobile or at [email protected] via email.
-
Hi Zany,
Thanks for the quick response. We are trying to extract/Export the employee expenses and post them as AP invoices in SAP via a load template(sap delivered). SAP needs a document date, and a posting date(both needs to be export date, please send the formula). I see we can use a formula for expense created, I want to see whats the formula for export date?
Regards
Ram B
-
Hi @Ram_Reddy
I think you are referring to the report export date. This should be the formula you are after:
{report:dateexported:yyyy-MM-dd}
Please refer to this help document for report based formulas: here
...and here for expense based formulas: here
Thank you.
