Introduction

Using LLM’s (ChatGPT) I helped an HR acquaintance create a formula to streamline their Excel spreadsheet tracking PTO. Using precise prompting, and designated cell assignment in excel, ChatGPT helped me create a robust/complex formula to calculate how many PTO days employees get, based on tenure and title factors, while also prorating any instances where an employee saw a mid-year increase in PTO due to promotion or tenure. I was able to do all of this without feeding any data from the sheet, ensuring data security.

The Process

  • Prompts:

    "How can I create an Excel formula that will take an employee’s hire date into account to tell me how many vacation days they qualify for? An employee who has less than two years tenure gets 10 days, an employee with 2 to 3 years gets 12 days, and an employee with four to 8 gets 15 days, and employees with nine or more years tenure gets in 20 days."

    "I also need to adjust the formula to also take into account default vacation minimum based on a separate column (start with C3) for "Title":

    Partner - 20

    Principal - 15

    Senior Associate - 15

    Associate - 10

    Senior Analyst - 10

    Analyst - 10"

    "Improve the formula to account for employee's who cross the threshold into a new PTO bracket during the year, and include prorated time on a monthly basis into the total. Only include prorated time if the start date is the 15th of the month or earlier. If the start date is after the 15th, start prorated PTO in the following month."

  • Prompt:

    "Please create an excel formula that makes the following calculations:

    1 - Calculate the vacation days based on tenure by the end of the current year (using the hire date in E3).

    2 - Calculate the vacation days based on the employee's title using nested IF statements to check the value in cell C3.

    3 - Use the MAX function to return the maximum value between the tenure-based and title-based vacation days.

    4 - Only for employee’s who switch tenure scenarios during the current year: Calculate the prorated number of vacation days based on the start date in E3. If the start date is the 15th of the month or earlier, it calculates the prorated number of vacation days from the current month. If the start date is after the 15th, the prorated number of vacation days starts in the following month.

    5 - Multiply the prorated amount by the new PTO maximum based on the end-of-year tenure.

    6 - Add the prorated portion to the original MAX total."

  • Prompt:

    “Break down my original formula into sections”

  • See an example of the actual formula on Google Sheets.