How to check PTO balance before approval with Power Automate, Microsoft Forms and Excel

Open the Excel file from Part 1. In the request tracker table, add two new columns: Leave Days and Requested Hours.

Leave Days stores how many days the employee asked for. Requested Hours stores how many PTO hours the request will use. For example, five days at eight hours a day is 40 hours.

The form itself does not change. It still asks for full name, email address, manager's email address, leave type, start date, end date and reason for leave.

In the same Excel file, create a second table and name it PTO Balance. Add these columns:

• Full Name
• Email Address
• Hours Per Day
• Total PTO Hours
• Used PTO Hours
• Available PTO Hours

Make Available PTO Hours a formula: Total PTO Hours minus Used PTO Hours. The flow only ever updates Used PTO Hours, and Excel recalculates the balance.

Add one row for every employee. You can change Hours Per Day or Total PTO Hours by hand at any time without breaking the flow, because the flow finds each row by email address.

Open the existing flow. After the step that generates the request ID, add the Excel Online (Business) action Get a row.

• Location: OneDrive for Business
• Document Library: OneDrive
• File: the same PTO tracker file
• Table: PTO Balance
• Key Column: Email Address
• Key Value: Email Address from Get response details

Rename the action to Get PTO Balance. The flow can now read the employee's hours per day, used hours and available hours.

Add a Compose action and rename it Leave Days. In the input, open the expression editor and enter an expression that subtracts the start date from the end date and adds one, for example:

add(div(sub(ticks('EndDate'),ticks('StartDate')),864000000000),1)

Replace the EndDate and StartDate placeholders with the End Date and Start Date answers from Get response details (dynamic content). The +1 is there because the start date counts as a leave day too.

Add another Compose action and rename it Requested Hours. Use an expression that multiplies leave days by hours per day:

mul(outputs('Leave_Days'),float('HoursPerDay'))

Replace the HoursPerDay placeholder with Hours Per Day from Get PTO Balance. Four leave days at eight hours gives 32 requested hours.

Open the existing Add a row into a table action that writes the request to the tracker. Under Advanced parameters, show Leave Days and Requested Hours. Map them to the outputs of the Leave Days and Requested Hours compose actions.

Every new request row now shows the days and hours requested.

Before the approval, add a Condition.

• Left side (expression): float() wrapped around Available PTO Hours from Get PTO Balance
• Operator: is greater than or equal to
• Right side: the output of Requested Hours

float() turns the Excel value into a number so Power Automate does not compare it as text. True means the employee has enough hours. False means they do not.

Drag the existing approval action into the True branch of the balance condition. Then drag the approval outcome condition in after it.

Now the manager only gets an approval request when the available hours cover the request.

Open the approval action. Keep the title, Assigned to (the manager's email from the form) and the original request details. Add three new lines to Details:

• Leave days: output of Leave Days
• Requested hours: output of Requested Hours
• Available PTO balance: Available PTO Hours from Get PTO Balance

The manager can now see the balance before deciding.

In the approved branch, keep the existing Update a row action that sets the tracker status to Approved (key column Request ID).

After it, add a second Update a row action:

• Location: OneDrive for Business, Document Library: OneDrive, same file
• Table: PTO Balance
• Key Column: Email Address, Key Value: Email Address from Get response details
• Under Advanced parameters, show Used PTO Hours

For Used PTO Hours, use an expression that adds the requested hours to the current used hours:

add(float('UsedPTOHours'),outputs('Requested_Hours'))

Replace the placeholder with Used PTO Hours from Get PTO Balance. Excel then recalculates Available PTO Hours on its own.

Open the existing approval email. Add a line Remaining PTO balance: followed by an expression that subtracts the requested hours from the available hours:

sub(float('AvailablePTOHours'),float(outputs('Requested_Hours')))

Replace the placeholder with Available PTO Hours from Get PTO Balance, then type hours after it.

The rejected branch stays the same as Part 1: it updates the tracker as Rejected with the comments and sends the rejection email.

In the False branch of the balance condition, add Update a row. The request was already added as Pending, so you only update that row.

• Same location, library and file
• Table: the request tracker (Table1 in the video)
• Key Column: Request ID, Key Value: the request ID variable
• Under Advanced parameters, set Status to Insufficient PTO Balance

Then add the Office 365 Outlook action Send an email (V2):

• To: Email Address from Get response details
• Subject: PTO request could not be submitted
• Body: greet the employee by Full Name and list leave days, requested hours and available PTO hours

The manager is never asked to approve a request that cannot be approved.

Save the flow and click Test, then Manually.

First, submit a request the employee can cover. In the video a three-day request goes to the manager, who sees leave days, requested hours and available hours. After approval the tracker shows 3 days and 24 hours, Used PTO Hours goes from 16 to 40, and the employee's email shows the remaining balance.

Next, submit a request larger than the balance. In the video the employee has 20 hours available and asks for 40. The tracker row gets Insufficient PTO Balance, no approval is sent, and the employee gets the "could not be submitted" email.