I need help with two things on an attendance tracker spreadsheet I'm working on.
1. I need to include holidays
2. I need the totals to exclude weekends and holidays.
2022 Annual-Leave-Tracker SAMPLE.xlsx
I need help with two things on an attendance tracker spreadsheet I'm working on.
1. I need to include holidays
2. I need the totals to exclude weekends and holidays.
2022 Annual-Leave-Tracker SAMPLE.xlsx
At first I was going to say, "Oh no, not another HR spreadsheet." Then I saw that you had your data normalized in a table on the Mark Leave spreadsheet. Good Job! That makes life a lot easier.
I didn't have to do much because your data is so well organized.
Since you have the Mark Leave Data in a table, you don't have to add space holder blank rows after the data. When you enter an employee ID on the line immediately below the table, it becomes part of the table and it copies down the formulas, formats and validations automatically. I added several extra columns: Weekend, Holiday and Use.
Weekend has the formula: =WEEKDAY([@Date],2)>5 this works out to be True if the date is a Saturday or Sunday.
Holiday has the formula =ISNUMBER(MATCH([@Date],Holidays,0)) this works out if the date is in the named range Holidays. This range "points to" a "perpetual holiday calendar" on the holidays tab. Unfortunately, these are U.S. Holidays. So you can look at some of the logic and rewrite it as a Canadian Holiday calendar and if you do, I'd like to see it. Or you can make a list of "hard dates" and repoint the name to the new range.
Finally, there is the Use column =NOT(OR(Table_Leave[@[Weekend]:[Holiday]])) this works out to be true if the date is NOT a weekend or holiday.
You had the right formulas on the summary sheet (I made this a table too - it makes the formulas easier to read). All you needed to add was the USE criteria.
There may be a way to use XLOOKUP on the Monthly Summary page, but I am not going to touch it since what you have apparently works just fine.
Once again, congratulations for using normalized data!
One spreadsheet to rule them all. One spreadsheet to find them. One spreadsheet to bring them all and at corporate, bind them.
A picture is worth a thousand words, but a sample spreadsheet is more likely to be worked on.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks