Question on "How to Create Leave Tracker in Excel" video

[kdakwa]

I am having several issues creating my individual leave tracker, one issue may be becasue I put the month sheet's table in a different spot that in the video.

I would like to
1. keep track of my total hours as the weeks pass by at my job. I get two weeks vacation per year (accumulated at 3.34 hours per pay period) and I accure sick leave on a monthly basis at 6.67 hours per month/3.34 hours per pay period,
2. I want to format SA and Su like in the video- but i am\formatting Wednesday and Thursday instead
3. I am having trouble pasting this formula into cell C10 as the video instructed:
=IFERROR(INDEX(Jan!AH$9:AH$13,MATCH($B10,Jan!$B$9:$B$13,0)),0)+IFERROR(INDEX(Feb!AH$9:AH$13,MATCH($B10,Feb!$B$9:$B$13,0)),0)+IFERROR(INDEX(Mar!AH$9:AH$13,MATCH($B10,Mar!$B$9:$B$13,0)),0)+IFERROR(INDEX(Apr!AH$9:AH$13,MATCH($B10,Apr!$B$9:$B$13,0)),0)+IFERROR(INDEX(May!AH$9:AH$13,MATCH($B10,May!$B$9:$B$13,0)),0)+IFERROR(INDEX(Jun!AH$9:AH$13,MATCH($B10,Jun!$B$9:$B$13,0)),0)+IFERROR(INDEX(Jul!AH$9:AH$13,MATCH($B10,Jul!$B$9:$B$13,0)),0)+IFERROR(INDEX(Aug!AH$9:AH$13,MATCH($B10,Aug!$B$9:$B$13,0)),0)+IFERROR(INDEX(Sep!AH$9:AH$13,MATCH($B10,Sep!$B$9:$B$13,0)),0)+IFERROR(INDEX(Oct!AH$9:AH$13,MATCH($B10,Oct!$B$9:$B$13,0)),0)+IFERROR(INDEX(Nov!AH$9:AH$13,MATCH($B10,Nov!$B$9:$B$13,0)),0)+IFERROR(INDEX(Dec!AH$9:AH$13,MATCH($B10,Dec!$B$9:$B$13,0)),0)
it is instructing me to "Update Values" and the file explorer appears, I am not allowed to do anythign with the formula...

Here is the link to the video.:

I am visual learner, so please feel free to email me and we can set up a teams meeting!

 

[shamimarita]

Hello Kdakwa,

Thank you for explaining the issues so clearly. Moving the monthly tables can definitely cause some of the formulas and formatting from the video to stop working. Here are solutions to each point.

1. Tracking vacation and sick accrual
Since you accrue 3.34 hours per pay period for both vacation and sick, the simplest method is to create a small running balance table.

For example:
Columns:
Pay Period | Vacation Earned | Vacation Used | Vacation Balance | Sick Earned | Sick Used | Sick Balance

Vacation Balance formula (starting in row 2):
=SUM($B$2:B2)-SUM($C$2:C2)

Sick Balance formula:
=SUM($E$2:E2)-SUM($F$2:F2)

Enter 3.34 in the “Earned” column each pay period and record any hours used. The balance will update automatically.

2. Saturday and Sunday formatting showing on Wed/Thu
This usually happens because the conditional formatting formula is referencing the wrong cells after the layout changed.

If your calendar uses real dates, use this formula in Conditional Formatting:
=WEEKDAY(C7,2)>5

Replace C7 with the first date cell in your calendar. The “2” setting makes Monday = 1 and Sunday = 7, so values greater than 5 are Saturday and Sunday.

Go to: Home → Conditional Formatting → Manage Rules → Edit Rule
Then check that the formula references the correct starting date cell for your layout.

3. “Update Values” message when entering the formula
That message appears when Excel thinks the formula is linking to another workbook.

Check the formula bar and see if it contains something like:
[WorkbookName.xlsx]Jan!AH$9:AH$13

If so, remove the part in brackets so it only reads:
Jan!AH$9:AH$13

Also make sure:

• Your sheet names exactly match Jan, Feb, Mar, etc. (no extra spaces)
• The ranges AH9:AH13 and B9:B13 still exist in those sheets

If you moved the monthly table, you must update AH$9:AH$13 and B$9:B$13 to match the new location of your data.