Financial Year Reporting
Last updated : 11/09/2019Workbooks has a number of functions that can be used within calculated columns to help simplify reporting on Financial Year information for records. These functions can be especially useful when working with Financial Reports to help with forecasting or cash flow calculations.
Workbooks has a number of functions that can be used within calculated columns to help simplify reporting on Financial Year information for records. These functions can be especially useful when working with Financial Reports to help with forecasting or cash flow calculations.
Below is a table of all available Financial Year reporting functions, as well as a brief overview of their function along with further detailed examples of how they can be used within a Report.
| Calculated Column | Description |
| FYEAR(expression) | Displays the calendar year of when the financial year starts of the specified date or date & time. |
| FYEARSTART(expression) | Shows the start date of the financial year of the specified date or date & time. |
| FYEAREND(expression) | Shows the end date of the financial year of the specified date or date & time. |
| FYEARNAME(expression) | Displays the name of the financial year of the specified date or date & time. |
| FQUARTER(expression) | Outputs a number (generally 1-4) for the Quarter within the financial year that the specified date or date & time falls in. |
| FQUARTERSTART(expression) | Shows the start date of the financial quarter of the specified date or date & time. |
| FQUARTEREND(expression) | Shows the end date of the financial quarter of the specified date or date & time. |
| FQUARTERNAME(expression) | Outputs the name of the Quarter within the financial year that the specified date or date & time falls in. |
| FYEARQUARTER(expression) | Converts a date or date & time column into a concatenation of the financial year and quarter in the format YYYYQ, where Q is generally a number between 1 and 4. |
| FPERIOD(expression) | Outputs a number between 1 and 13 based on where the date falls within your accounting periods. If you use quarters then the number will be between 1 and 4, calendar months between 1 and 12, and 4-4-5 week patterns between 1 and 13. |
| FPERIODNAME(expression) | Displays the short name of the financial period that the specified date or date & time falls in. |
| FPERIODFULLNAME(expression) | Displays the full name of the financial period that the specified date or date & time falls in. |
| FPERIODSTART(expression) | Shows the start date of the financial period of the specified date or date & time. |
| FPERIODEND(expression) | Shows the end date of the financial period of the specified date or date & time. |
| FYEARPERIOD(expression) | Converts a date or date & time into a concatenation of the financial year and quarter in the format YYYYPP. If your accounting periods are quarters then PP is a number between 01 and 04. If they are months then PP is between 01 and 12. If you use the 4-4-5 week pattern then PP is between 01 and 13. |
For the below examples we will use an Opportunity where the close date is the 15th November 2019 and the Financial Year runs from the 1st April 2019 to the 30th March 2020 and the Financial Year is broken into Calendar Months.
FYEAR(close_date) = 2019
This outputs the number 2019 as Financial year starts in 2019.
Note: Numbers are displayed in Workbooks Reports with thousand separators, so you will see 2,019. If you want to see the year displayed without the thousand separator, change the ‘Display as’ setting to “plain text” in the Advanced tab of the column.
FYEARSTART(close_date) = 01/04/2019
This outputs the start date of the financial year, which in this example is the 1st April 2019.
FYEAREND(close_date) = 31/03/2020
This outputs the end date of the financial year, which in this example is the 31st March 2020.
FYEARNAME(close_date) = FY2019/20
This outputs the name of the financial year that has been configured in Workbooks, which in this case has been configured as “FY2019/20”.
FQUARTER(close_date) = 3
This outputs the number 3 as November falls into the third quarter of the financial year between April and March.
FQUARTERSTART(close_date) = 01/10/2019
This outputs the start date of the Quarter this falls in which runs between October to December so the start date is 01/10/2019.
FQUARTEREND(close_date) = 31/12/2019
This outputs the end date of the Quarter this falls in which runs between October to December so the end date is 31/12/2019.
FQUARTERNAME(close_date) = Q3
This shows the name of the Quarter that the close date falls into, as the close date is within the third Quarter it will output Q3.
FYEARQUARTER(close_date) = 20193
This outputs the financial year followed by the number of the quarter that this falls into, as November is in the third quarter of the financial year starting in 2019 it shows 20193.
FPERIOD(close_date) = 8
As the Financial Year is broken up into Calendar Months and November is the 8th month from the start of the year in April, this outputs 8.
FPERIODNAME(close_date) = November
As the Financial Year is broken up into Calendar Months the Period Name will output as the name of the month, which in this case is “November”.
FPERIODFULLNAME(close_date) = FY2019/20 November
This outputs the full period name which starts with the name of the Financial Year followed by the Period Name which in this case is “FY2019/20 November”.
FPERIODSTART(close_date) = 01/11/2019
As the Financial Year is broken up into Calendar Months each month is a financial period meaning that it will use the start date of the month of the document close date which is 01/11/2019.
FPERIODEND(close_date) = 30/11/2019
As the Financial Year is broken up into Calendar Months each month is a financial period meaning that it will use the end date of the month of the document close date which is 30/11/2019.
FYEARPERIOD(close_date) = 201908
This outputs the financial year followed by the period number. As the Financial Year is broken up into Calendar Months, and the date falls in November, which is the 8th month of the financial year, the output is 201908.