Customer Forum

Transaction Document (Quote, Order, Invoice, Contract etc.) Import Tip to Populate Accounting Period Mandatory Field

Workbooks Support Posted: 2014-01-03 16:06

When importing Transaction Documents, such as Quotes, Orders, Invoices, Contracts etc., the Accounting Period field is mandatory, but you are unlikely to have this data in the correct format. You'll almost definitely have a field for Order Date, but nothing that specifies the Accounting Period, which is required by Workbooks.

A bit of Excel wizardry allows you to transform the date into a suitable value to import into the Accounting Period field.

e.g. Transform 15/11/2014 into FY2014 November.

Desired Output Example	Desired Output Description	Formulae
Accounting Period with year spanning 2 years and month name	FY2014/15 November	="FY"&TEXT(A1,"yyyy")&"/"&TEXT(A1,"yy")+1&" "&TEXT(A1,"mmmm")
Accounting Period with calendar year and month name	FY2014 November	="FY"&TEXT(A1,"yyyy")&" "&TEXT(A1,"mmmm")
Month name	January	=TEXT(A1,"mmmm")
2 digit year + 1	15	=TEXT(A1,"yy")+1
2 digit year	14	=TEXT(A1,"yy")
4 digit year	2014	=TEXT(A1,"yyyy")

Add an extra column to your import file.

Enter an appropriate formulae to transform the date into a suitable Accounting Period value.

The examples above should help. Please feel free to add additional tips as comments below.

This should help you satisfy the requirement to import a valid Accounting Period.

Nicky Posted: Mon, 15.05.2017 - 11:21

Hi, I'm not sure this first formula solves the problem of when your accounting period lies. To deal with this, you should use the formula, and replace the '10' with whichever month your accounting period starts in:

=IF(MONTH(K38)<10, "FY"&YEAR(K38)-1&"/"&RIGHT(YEAR(K38), 2)&" "&TEXT(K38, "mmmm"), "FY"&YEAR(K38)&"/"&RIGHT(YEAR(K38)+1, 2)&" "&TEXT(K38, "mmmm"))

Sam (Workbooks Online) Posted: Thu, 08.06.2017 - 08:29

Hi Nicky,

This is a definite improvement for taking into account the starting month of an Accounting Period.

Thank you very much for sharing this with us, it is greatly appreciated.

Kind Regards,

Sam