Combine staff leave tracker with their leave entitlement in Excel

Combine staff leave tracker with their leave entitlement in Excel. In this video I will show you how to combine a staff leave tracker with leave their remaining entitlement in Excel. It is good to track you staff leave against a schedule of time and against other employees that have leave at the same time. It is better however to see a running total of the days that they have remaining in each category.

• We have three sheets in the excel sheet
• The first sheet is the staff in a list against a yearly schedule
• On the second sheet we have the leave entitlement calculations
• We have employee names
• Start date and the end date is the first of January 2020
• Using this formula I have calculated the number of complete years as of January 1
• Using the values calculate I have used Vlookup and this table of hours to calculate how many hour of Vacation they have
• Now in this organization everybody is given 35 of personal time and 70 hours of sick time
• I have linked all these values to fields on the first page.
• For example on the number of sick hours subtracting the count of “S” that appears on the with in the year for that individual

Private Sub CommandButton1_Click()
Dim name As String
Dim leave As String
Dim rng As Range
Dim rownumber, monthmove As Integer
Dim Lstart, LEnd, difference, lnext As Integer

Dim x As Long

If TextBox1.Text = “” Then
MsgBox “Enter Name”
End If
‘name = TextBox1

name = Trim(TextBox1.Text)
Set rng = Sheet1.Columns(“A:A”).Find(What:=name, _
LookIn:=xlFormulas, LookAt:=xlWhole, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)
If rng Is Nothing Then
MsgBox “Name not found”
rownumber = rng.Row

End If
If cbleave.Value = “no pay” Then
leave = “np”
End If
If cbleave.Value = “Vacation” Then
leave = “V”
End If
If cbleave.Value = “Sick” Then
leave = “S”
End If
If cbleave.Value = “personal” Then
leave = “P”
End If

If cbmonths.Value = “January” Then
monthmove = 4
End If
If cbmonths.Value = “February” Then
monthmove = 35
End If

If cbmonths.Value = “March” Then
monthmove = 64
End If

If cbmonths.Value = -1 Then
MsgBox “No month Selected”
End If
‘MsgBox monthmove

Lstart = Trim(TextBox2.Text) + monthmove
LEnd = Trim(TextBox3.Text) + monthmove

Sheet1.Cells(rownumber, Lstart).Value = leave
Sheet1.Cells(rownumber, LEnd).Value = leave
difference = LEnd – Lstart
If difference(greater then symbol) 1 Then
lnext = Lstart
Do While lnext (less than symbol) LEnd
Sheet1.Cells(rownumber, lnext).Value = leave
lnext = lnext + 1
If lnext = LEnd Then
GoTo ende
End If
End If


End Sub

Private Sub CommandButton2_Click()
TextBox1.Text = “”
cbmonths.Value = “”
cbleave.Value = “”
TextBox2 = “”
TextBox3 = “”
End Sub

For more help visit my website or email me at

Contact me regarding customizing this template for your needs.

Excel one-on-one on-line training available. Email me to arrange.

I am able to provide online help on your computer at a reasonable rate.

Check out my next one-hour Excel Webinar

Check out Crowdcast for creating your webinars

If you need to buy Office 2019 follow

Follow me on Facebook

Follow me on twitter

IG @barbhendersonconsulting

You can help and generate a translation to you own language

*this description may contain affiliate links. When you click them, I may receive a small commission at no extra cost to you. I only recommend products and services that I’ve used or have experience with.

Watch more new videos about Excel Office | Synthesized by Mindovermetal English

5/5 - (1 bình chọn)

Bài viết liên quan

Theo dõi
Thông báo của
Cũ nhất
Mới nhất Được bỏ phiếu nhiều nhất
Phản hồi nội tuyến
Xem tất cả bình luận
Praveen G

So whenever new year we should add the. First year of the date in c column??

W Footballer 38

How to make that form and what if I need to select days from different months start and end date

Fraulein Maghuyop

Great video! But can you do this which accepts half day leave?

Humza Mahmood

Good video! A bit stuck please help.

The code finds the name of the person and is able to add the annual leave to that row however it does not perform this bit of the code.

Lstart = Trim(TextBox2.Text) + monthmove

LEnd = Trim(TextBox3.Text) + monthmove

In other words the annual leave is placed in the row number position from the number in TextBox2 and TextBox3. Therefore it is 4 days earlier than what it should be as it has not accommodated the "+ monthmove" part of the formula.

Any ideas? Thank you

Lars Inge Holen

Trying to follow you tutorial, but it doesnt work… Can I get a copy???

Ayse Niazi

Hello, could you please tell me if you have a tutorial video of how to add the, Enter leave button as shown on your spread sheet above? Thank you


thank you for this video i need file for this video.

Seti's VLOG's

Fantastic!! Can you help me with a simple Leave planner for my office which just takes the inputs from employees monthly for resource forecast…Great Video btw

asyraf syahmi

Can Google Spreadsheet App Script use those VBA script? Is it the same code?

Vicky hoyland

I’d love to implement this in our small business but my Excel doesn’t seem to be the same as this one – mine doesn’t have nearly as many tabs. I have file, home, insert, page layout, formulas, data, review, view and help. Could you help and advise on this please? Thank you

Barb Henderson

Thanks so much for watching! Let me know in the comments if this is going to work for you.

Barb Henderson

Thanks so much for watching! Let me know in the comments if this is going to work for you.