# Help with excel formulas

Copper Contributor

# Help with excel formulas

Good evening,

I am trying to create a holiday tracker to track my colleagues holiday allowance.

So my plan is the following.

Cell A1 hours Used
Cell B1 hours remaining
Cell C1 Annual holiday Allowance

So now let say the holiday allowance is 265. John Smith booked 10 hours so that 10 hours will be deducted from the 265 which would be 255. This part is easy enough just by doing

=SUM(A1-C1)

But the problem I have got is if John Smith booked a 5-hour holiday a few weeks later, and you updated the cell "Hours Used" from 10 hours to 5 hours, the total would be then 260. But realistically, it should be 250 instead due to previous holiday . So my question is how would I go about doing this. Any help or advice would be highly appreciated.

Thank you

3 Replies

# Re: Help with excel formulas

Subtracting within the same cell is tricky:

• You won't have a history of hour's used, and because of that:
• It's not easy to spot and to correct a mistake, or an alteration.

I'd do it - for example - like this:

The formulas in column B are:

You can fill down the formula from B3 downwards.

# Re: Help with excel formulas

Thank you, I had a feeling this would be the case. Thanks the for the advice :)

# Re: Help with excel formulas

Good evening Dafydd,

I understand your challenge in creating a holiday tracker in Excel that accurately reflects the cumulative hours used for holiday. Here is a user-friendly solution that addresses your need to track holiday usage over time.

Step-by-Step Solution:
Setting Up the Worksheet:

Cell A1: "Hours Used"
Cell B1: "Hours Remaining"
Cell C1: "Annual Holiday Allowance"
Cell D1: "Date of Usage" (Optional, for tracking dates of holiday usage)
Using a Table to Track Holiday Usage:

Instead of updating a single cell for hours used, use a table to log each holiday booking. This allows you to keep a record of all holidays taken.
Creating the Table:

Column A: "Date" (Date of the holiday)
Column B: "Hours Used" (Hours booked for that holiday)
Calculating Total Hours Used:

Use the SUM function to calculate the total hours used from the table.
Example: If your holiday bookings are in the range B2:B100, use =SUM(B2:B100).
Setting Up the Formulas:

Cell C1: Enter your annual holiday allowance (e.g., 265).
Cell A1: Use the formula to sum up the hours used from the table: =SUM(B2:B100).
Cell B1: Calculate the remaining hours by subtracting the total hours used from the annual allowance: =C1-A1.
Example Layout:
A B C D
Hours Used Hours Remaining Annual Holiday Allowance Date of Usage
0 (Formula) 265 (Formula) 265 (Manual Entry)
Date Hours Used
01/01/2024 10
15/02/2024 5
Cell A1 Formula: =SUM(B2:B100)
Cell B1 Formula: =C1-A1
This way, whenever John Smith or any colleague books a holiday, you simply add a new row in the table with the date and hours used. The tracker will automatically update the total hours used and the remaining hours.

Summary:
Use a table to log each holiday booking.
Sum the total hours used from the table.
Calculate the remaining hours by subtracting the total hours used from the annual allowance.
By following this method, you ensure that all holiday bookings are recorded and accounted for accurately, reflecting the true remaining holiday hours.

I hope this helps! If you have any further questions, feel free to ask.