--- title: "Use R365 GL Formulas" slug: "use-r365-gl-formulas" status: "new" updated: 2026-11-18T22:09:44Z published: 2026-11-18T22:09:44Z canonical: "docs.restaurant365.com/use-r365-gl-formulas" --- > ## Documentation Index > Fetch the complete documentation index at: https://docs.restaurant365.com/llms.txt > Use this file to discover all available pages before exploring further. # Use R365 GL Formulas In Beta This feature is in [beta](/doc/docs/user-research). Contact your CSM to learn more. The **R365.GL** formula pulls general ledger account balances from R365 into Excel. It builds profit and loss statements, tracks account activity by period, and compares results across locations inside a workbook the user controls, instead of exporting a canned report each time the user needs updated numbers. Because it is a formula, it can be combined with other Excel calculations, placed into an existing template, or refreshed for the whole workbook with one click. See [Install Excel Bridge](/doc/docs/install-excel-bridge) before writing a formula for the first time. ## Enter a Formula 1. Click a cell and type `=R365.GL(`. Excel lists R365.GL and R365.BUD under User Defined Functions while typing. 2. Review the argument hints. Excel shows `R365.GL(accounts, [locations], [startDate], [endDate], [approvedOnly])`. Arguments in brackets are optional. 3. Complete the formula and press **Enter**. The cell shows an hourglass. 4. In the task pane, click **Refresh Sheet** or **Refresh Workbook**. The cell fills in, and **Sheet/Workbook last updated** shows the refresh time. ![](https://cdn.us.document360.io/5c9d1111-0c69-4e96-8910-d7f2750fdc9d/Images/Documentation/image-KFCE1ZOB.png?sv=2026-02-06&spr=https&st=2026-11-18T23%3A08%3A08Z&se=2026-11-19T00%3A08%3A08Z&sr=b&sp=r&sig=ZZWfEqDb8l0%2ByANxwRlQSIcbBpBkISNBVra5h6XApM4%3D) > [!NOTE] > The task pane must stay open and signed in while working. Formulas get data only after the user clicks **Refresh Sheet** or **Refresh Workbook**; the task pane also adds quotes around unquoted dash numbers and dates automatically. --- ## What R365.GL Returns R365.GL returns the net activity (debits minus credits) for the accounts, locations, and date range specified, as a number. Data can be up to about 15 minutes behind R365; the task pane shows **Sheet / Workbook last updated** after each refresh. A formula returns data only for the accounts and locations the user's role can access. > [!NOTE] > **Note About Retained Earnings Balances** > > `R365.GL` does not include the year-end net income flip, so results will not match My Reports or the Balance Sheet for Retained Earnings accounts. Use My Reports as the source of truth for Retained Earnings. > > > > To tie out Retained Earnings in Excel: > > 1. Use `R365.GL` on the Retained Earnings account. > 2. Use `R365.GL` across all P&L accounts for all prior fiscal years through the last year-end. > 3. Sum the results. > > `R365.GL` supports up to 350 accounts per formula. If your chart of accounts has a large P&L range, split it across multiple `R365.GL` formulas and sum the totals. --- ## Syntax `=R365.GL(accounts, [locations], [startDate], [endDate], [approvedOnly])` | Argument | Required | What to enter | Examples | | --- | --- | --- | --- | | accounts | Yes | One account number, a comma-separated list, or a range using a colon. Up to 350 accounts per formula. | `5000` · `"5000,5100,5200"` · `"5000:5999"` | | locations | No | One location number, a list, or a range. Leave blank for all locations the role can access. | `1` · `"1,2,3"` · `"1:10"` | | startDate | No | A date. Leave blank for no start limit. | `DATE(2026,1,1)` · `"01/01/2026"` · a cell reference | | endDate | No | A date. Leave blank for no end limit. | `DATE(2026,1,31)` · `"01-31-2026"` · a cell reference | | approvedOnly | No | TRUE to include approved transactions only. FALSE or blank includes all. | `TRUE` | > [!WARNING] > Important: Argument Order Is Fixed > > Accounts first, then locations, start date, end date, and approved only. > [!TIP] > R365 Tip > > To see a description of each argument while building a formula, click **Insert Function** to open the **Function Arguments** dialog (Windows) or **Formula Builder** (Mac). Arguments can be entered on this dialog as well as directly in the cell. > > ![](https://cdn.us.document360.io/5c9d1111-0c69-4e96-8910-d7f2750fdc9d/Images/Documentation/image-HM8U3371.png?sv=2026-02-06&spr=https&st=2026-11-18T23%3A08%3A08Z&se=2026-11-19T00%3A08%3A08Z&sr=b&sp=r&sig=tKYk%2Bkeznx2ZSTuiJa2Gpn14p2ldoPmTPEbQX4LAh2U%3D) ## Quotation Marks A single value does not require quotes: `R365.GL(5000, 1, ...)` works as written. A list or range requires quotes, such as `"5000:5999"` or `"1,2,3"`. Without quotes, Excel reads `5000:5999` as a cell reference and `1,2,3` as separate arguments. A number with a dash or leading zeros, such as `1900-002` or `001`, needs quotes so R365 matches the number exactly. Without quotes, Excel drops leading zeros (`1900-002` becomes `1900-2`). The task pane adds quotes around what Excel passes, so R365 matches `1900-2` to `1900-002`. This works only while the task pane is open and signed in. A zero-padded location number with no dash, such as `001`, needs quotes. Without quotes, Excel passes `1`, which does not match `001`. > [!NOTE] > When two accounts could both match an unquoted number (for example, `1900-002` and `1900-02` both match `1900-2`), R365 returns a "no matching GL Codes" error. Quoting the full number resolves the ambiguity. ## Enter Dates A date can be `DATE(2026,1,1)`, a cell that contains a date, a quoted string such as `"01/01/2026"` or `"01-01-2026"` (mm/dd/yyyy or mm-dd-yyyy), or a typed value such as `6/1/2026`. The task pane adds quotes automatically. Typed dates require a current version of Excel. They work only when the formula starts with `=R365.GL(` directly, not nested inside another function such as `IF`. With no dates, a formula returns all dates. With only an end date, it returns everything up to that date. With only a start date, it returns that single day, not everything from the start date onward. A range needs both dates. A start date cannot be after the end date. ## Examples | Goal | Formula | | --- | --- | | One account, one location, one month, approved only | `R365.GL(5000, 1, DATE(2026,1,1), DATE(2026,1,31), TRUE)` | | Account range, all locations, a quarter | `R365.GL("5000:5999", , DATE(2026,1,1), DATE(2026,3,31))` | | Several accounts and locations, dates from cells | `R365.GL("5000,5100", "1,2,3", B1, B2, TRUE)` | | Typed dates | `R365.GL(14205, "001", 6/1/2026, 6/30/2026, TRUE)` | --- ## Limits A formula can cover up to 350 accounts by default. A formula that requests more than that returns "Error: This formula requests more than 350 GL accounts. Split it into several formulas." Each refresh can calculate up to 1,000 Excel Bridge formulas (R365.GL and R365.BUD combined**). Refresh Sheet** counts the formulas on the sheet in view, and **Refresh Workbook** counts the formulas on every sheet. When a refresh is over the limit, the task pane shows a message with the number of formulas. The message suggests moving some to another sheet or workbook. If your organization needs a higher limit, reach out to [R365 Support](https://help.restaurant365.net/support/home). --- ## Cell Errors When a formula cannot return a number, the cell shows an error message as text. SUM skips these cells. **Formula Problems** | Message | What to do | | --- | --- | | Error: Account numbers are required | Enter at least one account in the first argument. | | Error: Invalid account range notation | Use the form `"5000:5999"` with quotes. | | Error: Account range start must not be greater than end | Put the lower account number first. | | Error: Invalid location range notation | Use the form `"1:10"` with quotes. | | Error: Location range start must not be greater than end | Put the lower location number first. | | Error: Start date must not be after end date | Swap the dates. | | Error: Start date is not a valid date. Use "mm/dd/yyyy" or "mm-dd-yyyy". | Use `DATE()`, a date cell, or a quoted date in that format. | | Error: Invalid start date / Error: Invalid end date | A quoted date was not read as a valid date when the formula refreshed. Use `"mm/dd/yyyy"` or `DATE()`. | | Error: This formula requests more than 350 GL accounts. Split it into several formulas. | Split the formula into smaller account sets. | **No Matching Data** | Message | What to do | | --- | --- | | Error: The formula includes a value/range with no matching GL Codes | Check each account number and range. Every value must exist, and the user's role must be able to view it. A range must contain at least one accessible account. | | Error: The formula includes a value/range with no matching locations | Check each location the same way. | | Error: No account data available for the specified account number(s). Verify the account numbers and try again. | Check the account numbers. | | Error: No location data available for the specified location number(s). Verify the location numbers and try again. | Check the location numbers. | > [!NOTE] > These messages appear the same way when an account is missing and when the user's role cannot view it. See [Excel Bridge Troubleshooting](/doc/docs/excel-bridge-troubleshooting) for errors that are not specific to this formula, such as task pane messages and connection errors. --- --- ### FAQ #### Will two people see the same numbers if they open the same workbook? No. R365.GL returns data only for the accounts and locations that the current user's role can access. Two people with different role permissions can see different numbers from the same formula. This also means the numbers in a shared workbook can change depending on who last clicked **Refresh Sheet** or **Refresh Workbook**. #### Why do I need to quote account or location numbers sometimes but not always? A single account, location, or date does not need quotes, but a list or a range does. Without quotes, Excel reads a range like `5000:5999` as a cell reference instead of text. A number with a dash or a leading zero, such as `1900-002` or `001`, also needs quotes. The quotes make R365 match the number exactly instead of dropping characters. #### My formula cell is stuck on the hourglass and never fills in. What is wrong? A cell that has never had a value shows an hourglass until a refresh. It does not update on its own. If the cell still shows the hourglass after clicking **Refresh Sheet** or **Refresh Workbook**, the issue is usually a task pane connection or sign-in problem rather than the formula itself. See [Excel Bridge Troubleshooting](/doc/docs/excel-bridge-troubleshooting) for connection and sign-in errors. #### Can I use R365.GL to pull budgeted amounts instead of actuals? No. R365.GL returns general ledger account activity. R365.BUD returns budgeted amounts for an account, location, and date range. See [Write Budget Formulas](/doc/docs/use-r365-budget-formulas) for the budget formula's syntax.