Calculate processing time for AP: invoicesEden AccountsPayable

Feature Brief: Calculate processing time for AP invoices

You can calculate the processing time for a batch of invoices from the date they were received until the date they were paid. The result can help you determine department efficiency for up to a year. You can use either Microsoft® Excel® or SAP Crystal Reports® to calculate processing time.

In formulas to calculate processing time, you use the new Received Date field on the Invoice History form in Eden Accounts Payable. With theEden feature to export invoice history to Microsoft Excel, you can use Excel to calculate the difference between the received dateand thecheck/wire date(the invoice processing time), and then calculate the average.With SAP Crystal Reports, you create a custom report that includes a formula to calculate the number of days between the received date and check date, excluding Saturdays and Sundays.

Previously, the Received Date field was unavailable and you had to type this value into Excel to calculate processing time.

Note The four-day work week calculation is only available with Excel 2010.

Overview

Feature release: Eden v5.10

Areas impacted:

  • Invoice History form, Main tab, Received Date field
  • Invoices form, Main tab, Received Date field
  • Invoice Quick Entry form, Main tab, Received Date field
  • Import Invoices form

Required? No

Conditions

Received date for invoices has been entered and saved

New Functionality

Tracking the date an invoice is received

Calculate processing time for a batch of AP invoices –
Using a custom SAP Crystal Report

Calculate Processing time for a batch of AP invoices - Using Microsoft Excel

Export to Excel

Add holidays to Excel

Five-Day Workweek - Calculate the Efficiency

Calculate the processing time.

Calculate the average

Four-Day Workweek (or any work week) - Calculate the Efficiency

Calculate the processing time.

Calculate the average

Tracking the date an invoice is received

Record the date a department receives an invoiceusing the new Received Date field on the following forms:

  • Invoices form, Main tab
  • Invoice Quick Entry form, Main tab
  • Import Invoices form - Available for both dynamic and permanent file layout, but not required for import
  • Invoice History form, Main tab - A received date can still be entered for a post-paid invoice

This field is informational only. You can use it on Find (QBE) forms to select invoices and on Browse forms for reporting.

You could use the Received Date field as a benchmark to determine the efficiency with which a department processes invoices. You can either export invoice history to Excel and perform the calculations, or you can write a custom SAP Crystal Report and use the formula.

Calculate processing time for a batch of AP invoices – Using a custom SAP Crystal Report

An alternative to exporting to Excel and performing the calculations is to create a custom SAP Crystal Report and use the following formula. This Crystal formula calculates the number of days between the received date and check date, excluding Saturdays and Sundays:

Local DateTimeVar d1 := {ESAINHSH.RECEIVED_DATE};

Local DateTimeVard2 := {ESAINHSH.CHK_TRNFR_DATE};

DateDiff ("d", d1, d2) -

DateDiff ("ww", d1, d2, crSaturday) -

DateDiff ("ww", d1, d2, crSunday)

Calculate Processing time for a batch of AP invoices - Using Microsoft Excel

Export invoice data to Excel and add formulas to calculate the time spent on each invoice and the average processing time.

Export to Excel
  1. On the Invoice History form, choose Record menu Find (QBE)to select the desired invoices for a year.
  2. Open the Browse form by clicking the Browse button and prepare the file for export:

  1. Figure 1: Invoice History form, Main tab, Browse button.
  • To change the column order,drag and drop the columns.
  • The Date Received and Check Transfer Date fields should be displayed. If you don't see them,click the Show / hide button and check each field.
  • For any fields that you do not want to export,click the Show / hide button and clear the field.
  1. Choose File menuExport Excel.
  2. Enter the file name and click Save.

Note For instruction on using the Browse form, you can watch the 'Browse Form' videos in the Eden Forms library on Tyler Community.

Add holidays to Excel

Before you calculate the time it took to process invoices, you must add your agency's holidays to the Excel fileand formula to avoid counting them as work days.

  1. Copy the relevant holidays below. You only need to copy the left column, but you may copy the right column, too.

Holidays
01/01/20[xx] / New Year's Day
01/[xx]/ 20[xx] / Birthday of Martin Luther King Jr.
02/[xx]/20[xx] / Washington's Birthday (President's Day)
05/[xx]/20[xx] / Memorial Day
07/04/20[xx] / Independence Day
09/[xx]/20[xx] / Labor Day
10/[xx]/20[xx] / Columbus Day
11/11/20[xx] / Veterans Day
11/[xx]/20[xx] / Thanksgiving Day
12/25/20[xx] / Christmas Day
01/20/20[xx] / Inauguration Day (every four years)

Table 1: Template for holiday dates.

  1. Inside Excel, paste the holiday dates after the last invoice row.

Note Do not paste the holiday datesto the right of the table (orange column).

Figure 2: Holiday dates copied to the Excel file.

  1. Update the [xx]placeholders to the current year and days.

You are now ready to calculate invoice processing time using the method that matches the work week your agency follows:

  • For a five-day work week, refer to the 'Five Day Workweek – Calculate the Efficiency' section.
  • For a four-day work week (or any week that is not a five-day work week), refer to the 'Four-Day Workweek – Calculate the Efficiency' section.
Five-Day Workweek - Calculate the Efficiency

With the invoice information and holiday dates appearing on your Excel spreadsheet, you are ready to calculate the invoice processing time for a five-day work week.

Calculate the processing time.
  1. For the first invoice click the first cell to the right of the first-row.

Figure 3: The cell where formula will be entered.

  1. Enter the NETWORKDAYS formula into the formula field: =NETWORKDAYS(start_date, end_date,[holidays])

Replace start_date and end_date with the cells that hold those values for the invoice. In our example, the formula will use the Received Date from column H, row 2, as the start_date and the Check Transfer Date from column G, row 2, as the end_date of the processing time period.

Note You must select the Received Date value first (not the Check Transfer Date value). Otherwise, your calculations will be negative numbers.

Replace [holidays] with the range of rows where you entered the dates for holidays. For our example, enterthe cell range for holidays: C9 through C18.

Figure 4: The completed formula

The invoice processing time now displays inside the cell.

  1. Copy down the formula to the remaining rows. The rest of the rows fill with the invoice processing time.
Calculate the average
  1. To calculate the average processing time, click the first cell below the column of invoice processing values.

Figure 5: Cell for calculating average.

  1. Enter the AVERAGE formula into the formula field:

Figure 6: The completed average formula.

  1. Press Enter to calculate the average processing time for the batch of invoices.
Four-Day Workweek (or any work week) - Calculate the Efficiency

With the invoice information and holiday dates appearing on your Excel spreadsheet, you are ready to calculate the invoice processing time for a four-day work week.

Calculate the processing time.
  1. For the first invoice click the first cell to the right of the first-row.

Figure 7: The cell where formula will be entered.

  1. Enter the NETWORKDAYS.INTL formula into the Formula field. =NETWORKDAYS.INTL(start_date, end_date, [weekend], [holidays])

Replace start_date and end_date with the cells that hold those values for the invoice. In our example, the formula will use theReceived Date from column H, row 2, as the start_date and the Check Transfer Date from column G, row 2, as the end_date of the processing time period.

Note You must select the Received Date value first (not the Check Transfer Date value). Otherwise, your calculations will be negative numbers.

Replace [weekend]with the work-week string and[holidays] with the range of rows where you entered the dates for holidays. For our example, enter the work-week string value (enter a zero for a work day and a 1 for a weekend day) and cell range for holidays: "0000111" and C9 through C18.

Figure 8: The completed formula for a Monday-Thursday work-week.

The invoice processing time now displays inside the cell.

  1. Copy down the formula to the remaining rows. The rest of the rows fill with the invoice processing time.
Calculate the average
  1. To calculate the average processing time, click the first cell below the column of invoice processing values.

Figure 9: Cell for calculating average.

  1. Enter the AVERAGE formula into the Formula field:

Figure 10: The completed average formula.

  1. Press Enter to calculate the average processing time for the batch of invoices.

Tyler TechnologiesPage 1 of 7