# Gym Attendance Sheet in Excel: Free Layout and Formulas

> Build a gym attendance sheet in Excel or Google Sheets: member list, daily log, monthly grid, COUNTIFS and EDATE formulas, expiry highlights, and its limits.

- URL: https://www.mygymgate.com/blog/gym-attendance-sheet-format/
- Category: Operations
- Published: 2026-10-26
- Publisher: MyGymGate (https://www.mygymgate.com)

A workable gym attendance sheet in Excel or Google Sheets has three tabs: a **Members** sheet (one row per member with plan, start date and an end date from `=EDATE(start,months)-1`), a **Log** sheet (one row per visit: date, member ID, time), and a **monthly grid** that marks who came on which day using `COUNTIFS`. Add conditional formatting to colour expired and expiring members, and you have a free system that runs well for a small gym, as long as someone types every visit.

Below is the exact layout, the formulas, and the point where a sheet starts costing you more than it saves. The function behaviour is from Microsoft and Google's own help pages, linked as we go.

## The three-tab layout

| Tab | One row per | Columns | What it answers |
|---|---|---|---|
| Members | Member | ID, name, mobile, plan, months, start, end, days left, status, visits this month, last visit, days since visit | Who is active, expiring, expired or not coming |
| Log | Visit | Date, member ID, time in | Who came, and when |
| Grid | Member (rows) × day (columns) | ID, name, 1 to 31, total | A monthly view like a paper register |

Use a short **member ID** (M001, M002…) as the link between tabs. Names get misspelt; IDs don't.

## Tab 1: Members

Put headings in row 1 and the first member in row 2. In cell N1, put the first day of the current month: `=DATE(YEAR(TODAY()),MONTH(TODAY()),1)`.

| Col | Heading | Entry or formula (row 2) |
|---|---|---|
| A | ID | M001 |
| B | Name | typed |
| C | Mobile | typed |
| D | Plan | Monthly / Quarterly / Annual |
| E | Months | 1, 3, 6 or 12 |
| F | Start | date the plan starts |
| G | End | `=EDATE(F2,E2)-1` |
| H | Days left | `=G2-TODAY()` |
| I | Status | `=IF(H2<0,"Expired",IF(H2<=7,"Expiring","Active"))` |
| J | Visits this month | `=COUNTIFS(Log!$B$2:$B$20000,A2,Log!$A$2:$A$20000,">="&$N$1,Log!$A$2:$A$20000,"<"&EDATE($N$1,1))` |
| K | Last visit | `=MAXIFS(Log!$A$2:$A$20000,Log!$B$2:$B$20000,A2)` |
| L | Days since visit | `=IF(K2=0,"",TODAY()-K2)` |

Fill row 2's formulas down the column. A few notes on why they're written this way:

- **End date.** [EDATE](https://support.microsoft.com/en-us/office/edate-function-3c920eb2-6e66-44e7-a1f5-753ae47ee4f5) adds whole months to a date and returns a serial number, so format column G as a date. A non-integer month count is truncated, so 1.5 months becomes 1. The `-1` makes a plan starting 5 October end on 4 November, not 5 November. If your gym counts the end date differently, drop the `-1`. [Google Sheets' EDATE](https://support.google.com/docs/answer/3092974) behaves the same way.
- **Visits this month.** [COUNTIFS](https://support.microsoft.com/en-us/office/countifs-function-dda3dc6e-f74e-4aee-88bc-aa8c2a866842) counts rows that meet every condition: this member's ID, a date on or after the 1st, and before the 1st of next month. Criteria with a cell reference are joined with `&`, like `">="&$N$1`. Every range must be the same size, which is why all three say 2 to 20000. The [Google Sheets version](https://support.google.com/docs/answer/3256550) has the same rule.
- **Last visit.** [MAXIFS](https://support.microsoft.com/en-us/office/maxifs-function-dfd611e6-da2c-488a-919b-9b6376b28883) returns the latest date for that ID. It needs Excel 2019 or later (or Microsoft 365), and it returns 0 if the member has never visited, hence the `IF(K2=0,...)` in column L. [Google Sheets](https://support.google.com/docs/answer/7013817) also returns 0 when nothing matches.

Use fixed ranges like `$A$2:$A$20000` rather than whole columns. When the log approaches row 20,000, raise the number in every formula.

## Tab 2: Log

Three columns, nothing else:

| A: Date | B: Member ID | C: Time in |
|---|---|---|
| 05-10-2026 | M014 | 06:12 |
| 05-10-2026 | M003 | 06:20 |
| 05-10-2026 | M027 | 06:41 |

Rules that keep it clean:

1. One row per visit. Never edit old rows to "update" a member.
2. Dates as real dates, not text like "5 Oct". Format column A as a date.
3. Member ID only; let the Members tab hold names. If staff want to see the name, add a lookup in column D, but don't type names.
4. Type the time when the member walks in. A time typed at the end of the shift makes column C useless.

## Tab 3: Monthly grid

This recreates the familiar paper register, built from the log instead of typed twice.

- A2 down: member IDs (`=Members!A2`, filled down). B2 down: names (`=Members!B2`).
- C1: the first of the month, for example `=DATE(2026,10,1)`. D1: `=C1+1`, filled right to AG1 for 31 days.
- C2: `=IF(COUNTIFS(Log!$B$2:$B$20000,$A2,Log!$A$2:$A$20000,C$1)>0,1,"")`, filled across and down.
- AH2: `=SUM(C2:AG2)` for each member's visit count.
- Below the last member, a daily total: `=COUNTIFS(Log!$A$2:$A$20000,C$1)`, filled right.

The `$` signs matter: `$A2` keeps the ID column fixed as you fill right, and `C$1` keeps the date row fixed as you fill down. For a new month, change C1 and the whole grid redraws.

## Colour the members who need a call

Conditional formatting turns the Members tab into a to-do list.

**In Excel:** select A2:L500, go to Home > Conditional Formatting > New Rule, and choose "Use a formula to determine which cells to format". [Microsoft's guide](https://support.microsoft.com/en-us/office/use-conditional-formatting-to-highlight-information-in-excel-fed60dfa-1d3f-4e13-9ecb-f1951ff89d7f) notes the formula must start with `=` and return TRUE or FALSE.

**In Google Sheets:** select the range, go to Format > Conditional formatting, and pick "Custom formula is" ([Google's guide](https://support.google.com/docs/answer/78413)).

Add three rules:

| Colour | Formula | Meaning |
|---|---|---|
| Red | `=$H2<0` | Plan expired |
| Amber | `=AND($H2>=0,$H2<=7)` | Expires within a week |
| Grey | `=AND($H2>=0,$L2<>"",$L2>=10)` | Active but no visit in 10+ days |

Each formula is written for row 2 with `$` before the column letter, so the whole row colours. Pick your own inactivity threshold; 10 days is only an example.

One catch. Google's help page says conditional formatting formulas can only refer to cells on the same sheet, and you need `INDIRECT` to reach another. That's why days left (H) and days since visit (L) live on the Members tab: the rules only read from that tab.

Sort or filter by column I each morning, and the amber rows are your renewal calls for the day. For what to say, see [membership renewal reminders](/blog/gym-membership-renewal-reminders/).

## What the sheet tells you each month

- **Active members:** `=COUNTIFS(Members!I2:I500,"Active")+COUNTIFS(Members!I2:I500,"Expiring")`
- **Expired this month, not yet renewed:** `=COUNTIFS(Members!G2:G500,">="&Members!N1,Members!G2:G500,"<"&EDATE(Members!N1,1),Members!H2:H500,"<0")` (when a member renews, update their start date, so they drop out of this count)
- **Average visits per active member:** sum of column J for active rows ÷ active members

Say 20 of your 200 members expire and don't renew in a month: that's 10% churn. Run your own numbers through the [gym churn calculator](/tools/gym-churn-calculator/) to see what it costs in a year.

## Where a spreadsheet breaks down

File size is not the problem. An [Excel worksheet holds 1,048,576 rows](https://support.microsoft.com/en-us/office/excel-specifications-and-limits-1672b34d-7043-467e-8e27-269d656771c3), and [Google Sheets allows up to 20 million cells](https://support.google.com/drive/answer/37603) per file. A hypothetical gym with 150 check-ins a day adds about 4,500 log rows a month and under 55,000 a year.

The problems are human:

- **Every visit is typed.** At 6:30 pm with ten people at the desk, typing IDs creates a queue, and the person typing skips some. Missing rows make your inactive list wrong in both directions.
- **Nobody is checking the door.** A sheet records whoever staff type in. It doesn't stop an expired member walking past.
- **Versions drift.** A file emailed or sent on WhatsApp becomes three files. Use one shared Google Sheet, or one laptop, never copies.
- **It doesn't send anything.** The amber rows only help if someone opens the sheet and messages them.
- **It holds personal data.** Names and mobile numbers in a shared file are member data you're responsible for; restrict who has edit access.

A good rule of thumb: once typing check-ins takes more than a few minutes in your busiest hour, or the inactive list stops matching what you see on the floor, the log is the part to automate. [Paper register vs Excel vs software](/blog/gym-register-vs-software/) compares the options, and [how QR code check-in works](/blog/qr-code-gym-check-in/) explains the most common next step, where members scan in and the log fills itself. MyGymGate does that with any Android phone as the scanner, and produces the same expiring and inactive lists, for ₹399 a month.

## Quick checklist

- [ ] Three tabs: Members, Log, Grid
- [ ] Member IDs link every tab; names typed only once
- [ ] End date `=EDATE(start,months)-1`, formatted as a date
- [ ] COUNTIFS and MAXIFS with fixed, equal-size ranges
- [ ] Days left and days since visit on the Members tab (for Google Sheets formatting)
- [ ] Red, amber and grey rules on the member rows
- [ ] Log typed at entry, with real dates and times
- [ ] One shared file, edit access for staff who need it only
- [ ] Check amber rows every morning

## Useful links

- [COUNTIFS function (Microsoft)](https://support.microsoft.com/en-us/office/countifs-function-dda3dc6e-f74e-4aee-88bc-aa8c2a866842) and [COUNTIFS (Google Sheets)](https://support.google.com/docs/answer/3256550)
- [EDATE function (Microsoft)](https://support.microsoft.com/en-us/office/edate-function-3c920eb2-6e66-44e7-a1f5-753ae47ee4f5) and [EDATE (Google Sheets)](https://support.google.com/docs/answer/3092974)
- [MAXIFS function (Microsoft)](https://support.microsoft.com/en-us/office/maxifs-function-dfd611e6-da2c-488a-919b-9b6376b28883)
- [Conditional formatting in Excel (Microsoft)](https://support.microsoft.com/en-us/office/use-conditional-formatting-to-highlight-information-in-excel-fed60dfa-1d3f-4e13-9ecb-f1951ff89d7f)
- [Conditional formatting in Google Sheets (Google)](https://support.google.com/docs/answer/78413)
- [Excel specifications and limits (Microsoft)](https://support.microsoft.com/en-us/office/excel-specifications-and-limits-1672b34d-7043-467e-8e27-269d656771c3)

## Frequently asked questions

### Why does my expiry highlight work in Excel but not in Google Sheets?

Google Sheets conditional formatting formulas can only refer to cells on the same sheet; for another sheet you need INDIRECT. Keep the end date and days-left columns on the member sheet itself and point the rule at those.

### How do I calculate a gym plan's end date in Excel?

Use EDATE, which adds whole months to a start date. =EDATE(F2,E2)-1 gives the last valid day for a plan that starts on the date in F2 and lasts the number of months in E2. Format the cell as a date, because Excel returns a serial number.

### How many rows can a gym attendance sheet hold?

Far more than a gym needs. An Excel worksheet holds 1,048,576 rows, and a Google Sheets file up to 20 million cells. Even 150 check-ins a day is under 55,000 rows a year. Typing them in is the real limit, not the file.

### Can I track peak hours in an attendance sheet?

Yes, if the log has a time column. Count check-ins per hour with COUNTIFS on the time column, using one row per hour slot. It only works if staff type the actual entry time, not a rounded one at the end of the shift.