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

code
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"
Else
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
Loop
End If

ende:

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 easyexcelanswers@gmail.com.

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
easyexcelanswers

IG @barbhendersonconsulting

You can help and generate a translation to you own language

Combine staff leave tracker with their leave entitlement in Excel,track staff leave and absences,attendance,excel,annual leave,holiday,employee,calendar,vacation accrual calculator,vacation tracker spreadsheet,Barb Henderspn,sick leave,team management,attendance sheet in excel,leave tracker in excel,leave tracker xls,leave tracker excel sheet,leave tracker for team,leave tracker formula in excel,leave tracker,vacation tracker,