Deduct Partial Vacation Hours Taken from a Date or Date Range

**Kimston** · 01-13-2013, 06:56 PM

Before anything else, I want to thank everyone who contributes to Excelforum.com, especially those who help me on a daily basis. I’ve created an excel file where I can track vacation days or vacation hours taken by an employee. However, I’d like to know if there is a way to deduct partial hours taken by an employee from a specific date or date range excluding weekends and holidays. For instance an employee may opt to take 4 hours of vacation instead of the 8 hours. In the Accrual Balance and Vacation Records Excel file, there is a tab named “Vacation_Records” in which contains a column named “HoursTaken”. I'm able to count days taken by an employee from a date or date range excluding weekends and holidays, but is there a formula to simply deduct partial hours?
Again thank you very much for your time and dedication.

**Kimston** · 01-15-2013, 07:46 PM

Does anyone has an idea how I can accomplish this task? Thank you again for your valuable time.

**p24leclerc** · 02-09-2013, 11:19 AM

Hi,
here is a formula that will take hours into account:

Please Login or Register  to view this content.

Remember to press "CTRL" + "SHIFT" + Enter when you copy this formula to make it an ARRAY formula.

Here I assume that a partial day vacation is taken within the same day so you can enter the start hour and end hour of this vacation. The formula will not work if you enter hours in a vacation longer than a day.
Hours are calculated based on an 8 hors shift, so 4 hours is 0.5 day.
If a long vacation is started 4 hours before the actual day (early leave), you will have to enter this on 2 rows. One for the hours and the other for the days.
Hope this helps.

**Kimston** · 02-09-2013, 09:27 PM

Pierre Leclerc,
I went and created a new file based on your provided formula and added new columns from I to L. However, after testing your formula, I noticed when I filled down the formula down column K, I get 1 if there is no time in column I and J. Also, when I deducted 8 hours from Start Hour and End Hour, I get 1 in column K. If, for example, I add all the hours for Kathy Johnson, I should get 78 hours taken and not 67.5.
I wonder if I am doing it correctly. Please take a look at the new attached file and let me know what I am doing wrong.
Again,
Thank you very much for helping me with this task.

**p24leclerc** · 02-10-2013, 10:46 PM

Look at the attached file.
you don't have to add new columns. The formula look at your column C and D dates and calculate the days or hours of vacation taken.
The only difference between the 2 (days or hours) is that you enter a date with a time as a start and end date for a person taking partial day vacation.

**Kimston** · 02-11-2013, 12:00 AM

Mr. Pierre Leclerc, I am very very impressed with your work and knowledge. You are right! I don't need new columns to complete that task. Thank you very much. I don't have enough words to say.
Sincerely,
Kimston