Share via

Annual Leave Excel Sheet to caluclate Annual Leave up to date

Anonymous
2021-07-08T00:52:49+00:00

Hi, I am a noob to excel sheet formula.

I would like to ask all gurus to help.

I need to create a excel sheet that can automatically tracks the staff's annual leave up to date.

Say the staff is entitled to 20 days of leave a year. So if say in June, the staff would have accrued 10 days of annual leave.

And in additional, I would also like to have the formular for staff who say join in March & his entitlement of leave is also 20 days a year. So in July, he would have accrued ### days of leave to date.

Thank you.

Microsoft 365 and Office | Excel | For business | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

Answer accepted by question author

  1. Anonymous
    2021-07-11T17:05:43+00:00

    Here are some Vacation tracker templates you may be able to adapt to your need

    PTO- Accrued Vacation and Time Off Template     2017 03 30
    https://howtoexcel.net/2017/03/accrued-vacation-and-time-off-template.html
    Tracking employee vacation and time-off requests can be a bit of a headache. This template will help track time off as well as how much vacation has been used up and how much is remaining. It also helps to prepare your recurring journal entries to expense vacation.
    .

    PTO- Annual Employee Leave (PTO Tracker) Excel format          2019 07 10
    https://www.excel124.net/employee-templates/pto-tracker-excel/
    Are you want to manage employee leaves record or accrual (holiday) plan. It’s very important to manage or track all employee records basis on their activities. In other words, you can call the employee leave tracker template. In this article, you can download PTO TRACKER Excel Template Free or Premium version.
    .

    PTO- Calculate Paid Vacation for Hourly Employees
    https://buddypunch.com/blog/calculate-vacation-hourly-employees/
    Calculating PTO (Paid Time Off) can be a confusing task for some employers. It all depends on which “accrual rate” you decide to use. The first step to track employee vacation time is to decide how many hours you want to allocate to full-time employees each year. Based on a 40-hour work week, you may want to give 40 hours (1 week of vacation time), 80 hours (2 weeks), or some other number in between. Based on the accrual method you choose, you can calculate how much time employees accrue each pay period.
    .

    PTO- Calculate Vacation Accrual (online calculatorhttps://fitsmallbusiness.com/how-to-calculate-vacation-accrual/
    Small businesses offering paid time off (PTO) must manage accruals accurately to avoid labor law violations. Our vacation - PTO accrual calculator lets you determine the appropriate accrual rate to use per pay period based on your business work days, hours in a work week, and how many days of PTO - vacation you offer each year.
    .

    PTO- Employee PTO Tracker & Calculator 2020 - accrue by service yearshttps://indzara.com/2015/09/employee-pto-tracker-free-excel-template/
    Do you want to easily find out how many days of PTO (Paid Time Off) you have available?
    .  *  Several settings available to cover most common business PTO policy scenarios
    .  *  Very flexible and easy to customize for your specific business needs
    .  *  Automatically calculates PTO balances for today and any future date
    .  *  Vacation dates can be entered as date ranges
    .  *  File is designed for one employee only. Make a copy of the workbook to use for the second employee.
    .

    PTO- Employee PTO Tracker Excel Template Free            2016 11 23
    https://www.xlstemplates.com/employee-pto-tracker-excel-template-free/
    .

    PTO- Employee Vacation Tracker Templates
    https://www.getexceltemplates.com/employee-vacation-tracker-template.html
    Running a big organization with large number of employees could be a big deal for you if you are not using an employee vacation tracker template. It provides the business management a proper way to track and manage vacations for employees in order to carry out business operations and processes without facing manpower problems. Employee vacation tracker is a vacation management tool used by almost all bug business organizations and companies to manage paid time off (PTO) and emergency leaves. Setting up an employee vacation tracker from scratch sounds like difficult job but having an editable employee vacation tracker template makes the work easier.
    .

    PTO- Paid Time Off Balance Calculator for Hourly Employee     2019 10 25
    https://indzara.com/2019/11/pto-balance-calculator-for-hourly-employee/  
    a Simple & Effective solution to calculate and track Paid-Time-Off (PTO) for your hourly employee
    Small businesses offering paid time off (PTO) must manage accruals accurately to avoid labor law violations. Our vacation - PTO accrual calculator lets you determine the appropriate accrual rate to use per pay period based on your business work days, hours in a work week, and how many days of PTO - vacation you offer each year.
    .

    4 people found this answer helpful.
    0 comments

4 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2021-07-14T16:23:33+00:00

    Hi PopCornPals,

    I'm writing this reply to follow up on this thread, may I know if you have checked the replies above? When you have time, you are welcome to come back and let us know if you still need help here.

    Hope you are all well during this period, stay safe and have a nice day : )

    Best Regards,

    Arck

    0 comments
  2. Anonymous
    2021-07-11T09:22:46+00:00

    Hi PopCornPals,

    Sorry for the late response due to out of the office.

    The [@[Join Date]] refers to the corresponding table header, in your case, if you didn't format the range as a table, you can format it as table with the same header as mine. Or if you have formatted it as a table but had different header names you can modify the header name or the formula.

    Best Regards,

    Arck

    0 comments
  3. Anonymous
    2021-07-08T10:03:34+00:00

    Hi PopCornPals,

     

    Thanks for using Microsoft products and posting in the community, I'm glad to offer help.

     

    According to your description, if I didn't misunderstand your question, you may want to implement the following requirements:

     

    1. If the staff joined in the first half of the year, he/she is entitled to 20 days of leave a year, and accrued 10 days of annual leave every half year.
    2. If the staff joined in the second half of the year, he/she is entitled to 10 days of leave the current year. Rule #1 starts from the next year.
    3. Unused leave is cleared at the end of the year.

     

    If that is the case, you can use the following formula:

    =IF(YEAR(TODAY())=YEAR([@[Join Date]]),IF(MONTH(TODAY())<7,10,IF(MONTH([@[Join Date]])<7,20,10)),IF(MONTH(TODAY())<7,10,20))

    ****![Image](https://learn-attachment.microsoft.com/api/attachments/7546d308-308d-41c9-9dbd-8c72f8185c09?platform=QnA"https://support.microsoft.com/en-us/office/if-function-69aed7c9-4e8a-4755-a9bc-aa8bbff73be2" rel="ugc nofollow">IF function - Office Support

    -      YEAR function - Office Support

    -      MONTH function - Office Support

     

    If I have any misunderstanding of your situation or you have any concerns, please feel free to let us know.

     

    Best Regards,

    Arck

    Hihi Arck,

    Thank you so much for helping. However, I copy & paste the formula but prompts me something. Did I do something wrong?

    0 comments
  4. Anonymous
    2021-07-08T08:14:08+00:00

    Hi PopCornPals,

    Thanks for using Microsoft products and posting in the community, I'm glad to offer help.

    According to your description, if I didn't misunderstand your question, you may want to implement the following requirements:

    1. If the staff joined in the first half of the year, he/she is entitled to 20 days of leave a year, and accrued 10 days of annual leave every half year.
    2. If the staff joined in the second half of the year, he/she is entitled to 10 days of leave the current year. Rule #1 starts from the next year.
    3. Unused leave is cleared at the end of the year.

    If that is the case, you can use the following formula:

    =IF(YEAR(TODAY())=YEAR([@[Join Date]]),IF(MONTH(TODAY())<7,10,IF(MONTH([@[Join Date]])<7,20,10)),IF(MONTH(TODAY())<7,10,20))

    ****![](https://learn-attachment.microsoft.com/api/attachments/7546d308-308d-41c9-9dbd-8c72f8185c09?platform=QnA"https://support.microsoft.com/en-us/office/if-function-69aed7c9-4e8a-4755-a9bc-aa8bbff73be2" rel="ugc nofollow">IF function - Office Support

    -      YEAR function - Office Support

    -      MONTH function - Office Support

    If I have any misunderstanding of your situation or you have any concerns, please feel free to let us know.

    Best Regards,

    Arck

    0 comments