How to Keep Track of Vacation Days in Excel

Create a simple Excel vacation tracker with start and end dates, leave type, approval status, and a list of holidays; use a workday formula to count weekdays and a balance cell to show approved days used. Before you begin, confirm your annual allowance, the dates your workplace observes as holidays, and whether your leave year follows the calendar year.

What You’ll Need

Microsoft Excel for Microsoft 365 on Windows. The formulas below also work in Excel for Mac and Excel for the web, though ribbon layouts may differ slightly.
Your vacation allowance for the year and the dates of public or company holidays that do not count against that allowance.
A decision about how you want to record requests. This guide uses one row per request and tracks whole weekdays; part-days and unusual work schedules may need a manual adjustment.

Step-by-Step

Start a workbook for one leave year. Open a blank workbook and rename the first worksheet, for example, “Vacation 2026.” Keeping each calendar year on its own sheet makes the balance easier to review and avoids mixing last year’s requests with this year’s. If your employer uses a different leave year, name the sheet for that period and use the same dates consistently. Save the workbook somewhere secure and accessible to you.
Create the request log headings. In row 1, enter these headings: A1 “Start Date,” B1 “End Date,” C1 “Leave Type,” D1 “Status,” E1 “Requested Days,” and F1 “Charged Days.” Each request gets its own row starting in row 2. Add a request as soon as it is submitted, then update its status when it is approved or declined. You can type “Vacation,” “Personal,” or another category in Leave Type, but use a consistent spelling so the entries are easy to filter.
Add the holiday list and allowance area. Enter “Holiday Dates” in H1 and list the dates your workplace excludes from vacation calculations in H2:H20, one date per cell. Replace the example range with a longer one if needed. In J1, type “Annual Allowance”; enter your available days as a number in J2, such as 15. In J3, enter “Approved Used,” and in J4, enter “Remaining.” Use the allowance your employer has confirmed, rather than assuming weekends or public holidays are handled the same way everywhere.
Format dates and make entries easier to read. Select columns A:B and H, then choose Home > Number Format and select Short Date. Format E:F and J2:J4 as Number, with zero decimal places if you only use whole days. Select the request area and choose Home > Format as Table if you want filter arrows and alternating row shading. If you make it a table, leave the formula columns in place and copy their formulas into new rows when you add requests. Dates stored as text can cause calculations to fail, so check that Excel recognizes each entry as a date.
Calculate the weekdays in each request. With start dates in A2:A101, end dates in B2:B101, statuses in D2:D101, and holiday dates in H2:H20, enter this formula in E2. It counts weekdays inclusively between the two dates and excludes any dates in the holiday range: =IF(OR(A2="",B2=""),"",NETWORKDAYS.INTL(A2,B2,1,$H$2:$H$20)). For example, a Monday-to-Friday request with a holiday on Wednesday returns 4. The weekend argument 1 means Saturday and Sunday are nonworking days. Copy the formula down the column for the rows you plan to use. On Windows, select E2:E101 and press Ctrl+D to fill down after putting the formula in E2; on Mac, use Home > Fill > Down if the shortcut differs on your keyboard.
Count only approved time against the balance. With the same layout, enter this formula in F2 to charge days only when the status in D is exactly “Approved”: =IF(AND(D2="Approved",ISNUMBER(E2)),E2,0). A pending or declined request returns 0 in Charged Days, even though its weekdays can still appear in Requested Days. Copy the formula down F2:F101. Use the same status spelling and capitalization for every request; the formula checks the text “Approved.”
Calculate used days and the remaining balance. With approved charged days in F2:F101 and the allowance entered as a number in J2, put this formula in J3: =SUM(F2:F101). If three approved requests use 3, 2, and 1 days, J3 returns 6. With the allowance in J2 and the total used in J3, put this formula in J4: =J2-J3. If J2 is 15 and J3 is 6, J4 returns 9 days remaining. A negative result means approved leave exceeds the allowance you entered, so verify the figures and check your workplace’s rules.
Test the tracker, then keep it current. Enter a sample weekday request and confirm that E counts the expected days. Change its status from “Pending” to “Approved” and check that F and J3 increase while J4 decreases. Remove the sample when you finish. For each real request, enter both dates, leave type, and status; update the status after a decision; and check the balance after approval. If you need more than 100 requests, extend the formulas and the SUM range so new rows are included.

Common Mistakes

Counting weekends by hand or forgetting holidays: NETWORKDAYS.INTL excludes Saturday and Sunday with the setting used above, but it does not know your workplace’s holiday calendar. Add the relevant holiday dates to column H as real Excel dates. If your workplace works a different weekend pattern, the argument 1 may not fit; check the function’s weekend options and use the correct setting for your schedule.

Using inconsistent status labels: “Approved” is what the Charged Days formula looks for. Labels such as “OK,” “approved,” or “Awaiting” will not match that exact test. Pick a short list of status terms and reuse them. You can also select D2:D101 and choose Data > Data Validation > Data Validation, then set Allow to List and enter Pending,Approved,Declined as the source. This helps prevent spelling differences.

Mixing leave years or entering text as dates: A request that crosses into another leave year can be counted wholly on the wrong sheet if you do not split it. For a calendar-year tracker, divide a cross-year request into one row for the days in each year and use each year’s holiday list. If Excel does not calculate a date, re-enter it in a recognized date format instead of pasting text.

Counting all requests as used: Requested Days is not the same as approved leave. The tracker deliberately adds only the Charged Days column to the balance. Do not replace the J3 formula with a sum of E if you want pending and declined requests excluded.

Tips

Freeze the headings so they remain visible as the log grows: select A2 and choose View > Freeze Panes > Freeze Panes. Use the filter arrows, if you formatted the range as a table, to view only pending requests or a particular leave type. Add an optional Notes column for request references or reminders, but avoid storing sensitive personal details in a shared workbook.

To make a low balance easier to spot, select J4 and choose Home > Conditional Formatting > Highlight Cells Rules > Less Than. Enter a threshold, such as 3, and choose a format. Treat the result as a visual alert, not as a policy decision. Keep a backup copy and check your organization’s official leave record periodically, especially if carryover, accrual, part-time schedules, or special leave categories affect your allowance.

If your employer deducts half-days, the weekday formula will still return whole days. You can adjust the charged value for a specific request manually, or add a separate half-day field and agree on a clear calculation method before relying on the balance. For rotating shifts or nonstandard weekends, confirm how your employer counts a day of leave before adapting the weekend setting.

Troubleshooting

The formula displays #NUM!: Check that the end date in column B is not earlier than the start date in column A. For a request crossing a year boundary, split it into separate rows if you are keeping one sheet per year.

The formula displays #VALUE! or counts an unexpected number: Confirm that the start date, end date, and holiday entries are valid Excel dates rather than text. Check that the holiday list contains only dates and that the weekend setting matches your normal workweek. The formula counts both the start and end dates when they are working days, so a Friday-to-Monday request normally counts as 2 days, not 4.

The remaining balance does not change: Check that the status cell says exactly “Approved,” that the Charged Days formula was copied to that row, and that the J3 SUM range includes the row. If the status is still pending, a zero charged value is expected. Also confirm that J2 contains a number, not text such as “15 days.”

New rows are not included: The example formulas cover rows 2 through 101. If you entered a request below row 101, fill the formulas down and expand the range in J3. If you use an Excel table, add the new request directly beneath it so the table can extend; verify that formula columns populate as expected.

Excel shows the formula as text: Set the formula cell’s format to General, then select the cell, press F2, and press Enter. Remove any leading apostrophe before the equals sign. In Excel for Mac or the web, the same formula syntax works; if your regional settings require semicolons instead of commas, use the separator Excel expects in that installation.

Frequently Asked Questions

Does this tracker count weekends?
Not with the formula shown. Its weekend setting excludes Saturday and Sunday, and it counts weekdays between the start and end dates inclusively. If your schedule uses different nonworking days, adjust the weekend argument to suit it.

Does it automatically know public holidays?
No. Enter the dates that your workplace excludes in H2:H20, and make sure they are actual Excel dates. The formula ignores listed holidays that fall within a request; it does not look up a country’s holiday calendar for you.

Should pending vacation requests reduce my balance?
This setup keeps pending requests visible in Requested Days but does not deduct them from Approved Used. If you want to see a projected balance that includes pending requests, add a separate calculation rather than changing the approved balance without making the distinction clear.

Can I track more than one employee with this layout?
Yes, but add an Employee column and create a separate balance calculation for each person. For a personal tracker, one workbook or sheet per employee is simpler and reduces the risk of combining allowances or requests.

Can I use the tracker for half-days?
The supplied formula counts whole working days. You can record half-days by adjusting the charged amount manually or by adding a clearly defined field for partial days, but confirm your employer’s counting rules first.

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.