Tracking Holiday Days Taken Each Year

[hajjar]

Hi folks,

I'm new to this, so I hope I'm doing this right. I'm trying to track the total number of vacation days taken by each employee per year as well as show the eligible and number of remaining days each year. I have created an independent tab for each employee and part of the info is as follows:
VACATION DETAILS
FROM TO ELIGIBLE TAKEN REM.
17-Jul-11 18-Jul-11 5 1 5
25-Jul-11 26-Jul-11 0 2 0
15-Sep-11 16-Sep-11 5 2 5
I need a formula that looks through "FROM" and totals all the vacation days taken in 2011 (keeping in mind that the "FROM" column will contain dates from all of the years the employee has been with the company). As you can see from the above results, my current attempt(s) have failed miserably. In 2 other columns to the right, I have inserted date ranges for each year (i.e. 01-Jan-09 31-Dec-09 etc.) and the current attempted formula is: =IFERROR(IF($G7>0,5-SUMIFS($L$6:$L$14,$G$6:$G$14,">="&$V7,$G$6:$G$14,"<="&$W7),""),"") Sorry for the ugly sample but I don't know how to attach the file here.
I sure could use some help with this.
Thanks,
GH

[newdoverman]

I suggest that you set up like a bank book. List the dates down column A, the employee name in column B and a code for the leave taken in column C. You can then use a Pivot table to extract the statistics for each person over the entire period of time. There are also other methods of extracting the statistics that you will require.

Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

[hajjar]

Thanks newdoverman. In the attached example, I am looking to achieve the following results:
Column K (Eligible): confirms eligibility based on (a) time elapsed (1yr) from the date of request (column G) and (b) based on the number of days taken, it provides the balance of remaining days for the current year which should be identical to Column M (Rem). I can't use an opening balance based on Column M (Rem) because there is always the likelihood that an employee doesn't use his/her entire allotted vacation days and we don't have a carryover policy. Therefore, Column K (Eligible) should be looking through columns G & I (From & To) to calculate the total number of days taken for that employee's year and returning a balance. I hope all this gibberish made sense.
HR_Spreadsheet_Beta.xlsx
Thanks for the help!

[newdoverman]

First of all I unmerged a lot of cells that I would be working with as merged cells can cause a lot of confusion if not problems with calculations.
I inserted a column between TO and Eligible called Years Serv. and entered this formula in J7 to calculate years of service.:

=DATEDIF($H$2,H7,"y")*1

In K7 I entered this formula to reset the days Eligible when an anniversary of service is passed. If the anniversary has passed, days taken since the anniversary are subtracted from the Eligible:

=IF(J7="","",IF(J7>J6,5,M6))

I amended the NETWORKDAYS formula to use a cell range instead of hard-coded date serial numbers.

=IF(K7="","",NETWORKDAYS($H7,$I7,$V$6:$W$16))

[hajjar]

Thank you so much for this. I've been pounding my head for a while trying to find the best formula to capture the data. Regarding the NETWORKDAYS formula, the only reason I used hard coded data was for the Canadian statutory holidays. Is there any other way to have the TAKEN column account for them?

[newdoverman]

All that you need is a range with the holidays, in your case $V$6:$W$16 and that is all there is to it. If the NETWORKDAYS start and end dates include one of the dates listed in the range, it will be subtracted from the number of days.

[hajjar]

Got it! Again, thanks so much for this. I'm trying to use Excel to help manage HR transactions and provide me statistical reporting. This is the 1st step in setting up some of the data collection I'm looking for. I really appreciate the quick replies.

[newdoverman]

You might find it even better to use the NETWORKDAYS.INTL that also has a weekend element to it that defines what a weekend is for your company. Is it Saturday and Sunday, Sunday and Monday, Monday and Tuesday etc. This works like the holidays in that the defined weekend days are not counted as workdays.

[hajjar]

Although we work many weekends, they are in addition to the work week so the conventional weekend is still considered the weekend. What I've done so far, is input all the holiday dates up to 2018. I figure something may likely come out over the next 3 years that may make it even easier. If not, I'll either write a macro or group all my sheets and update the next 3 years thereafter.