Check leave status in SharePoint and manage approvals cleanly with Power Automate

How do you check in Power Automate whether a person has already entered vacation or an absence during a specific period, and how can this check be linked with an approval workflow? The answer depends primarily on the absence list's data model. If date fields are set correctly and the absence status is stored in a clear status field, the check can be solved with a simple OData filter query. Once time zone, overlap detection, and coverage rules are added, the model quickly becomes more complex.

This guide describes how a SharePoint Power Automate vacation status process is built: from the list through the check logic to the approval and notification. For the general process architecture, Power Automate SharePoint processes is the appropriate foundational article.

The data model for vacation, absence, and approval status

The data model is the foundation for all downstream automations. Unclear or inconsistent fields in the absence list make every check logic fragile.

Recommended column structure for an absence list

Column name: Person

Column type: Person column

Purpose: The affected person

Column name: From

Column type: Date and time

Purpose: First absence day

Column name: To

Column type: Date and time

Purpose: Last absence day

Column name: Type

Column type: Choice

Purpose: Vacation, sick leave, half-day, business trip, other

Column name: Status

Column type: Choice

Purpose: Requested, approved, rejected, canceled

Column name: Coverage

Column type: Person column

Purpose: Who covers during the absence

Column name: Approved by

Column type: Person column

Purpose: Who approved

Column name: Approval date

Column type: Date and time

Purpose: When it was approved

Column name: Comment

Column type: Multi-line text

Purpose: Justification or note

Column name: FlowLog

Column type: Multi-line text

Purpose: Technical Flow outputs for debugging (optional)

Why date and time is more important than just date

Storing date and time instead of just the date is important for correct overlap checks. If only the date is stored, SharePoint internally calculates from midnight UTC, which can lead to incorrect comparisons once time zone differences exist between the tenant and the user location.

For an absence period that applies for the entire day, it is recommended to set the "From" date to 00:00:00 UTC and the "To" date to 23:59:59 UTC, adjusted to the tenant time zone. Alternatively, time zone conversion can be performed in the Flow using convertTimeZone().

Define the status model clearly

The status of an absence request should always reflect the current approval state. A request that has neither been approved nor rejected must not be considered a "valid absence" for other check logic. Therefore, the overlap check should only respond to entries with the status "Approved".

Check before an action if a person is already absent

The most common requirement is: Before an assignment is made, a task is created, or a resource is booked, it should be checked whether the relevant person is absent during the relevant period. If this results in tasks in SharePoint, the status and trigger model from Connect SharePoint tasks with Power Automate helps.

Query with odata filter

The action "Get items" in Power Automate enables a filtered query of the SharePoint list. For an overlap check with a defined period, the OData filter is:

Person/EMail eq 'user@domain.com' and Status eq 'Genehmigt' and Von le '2024-07-15T23:59:59Z' and Bis ge '2024-07-10T00:00:00Z'

This filter returns all approved absence entries for the specified person that overlap with the period from July 10 to July 15, 2024. The logic behind this: An entry overlaps with the searched period if its end is after or equal to the start of the searched period AND its start is before or equal to the end of the searched period.

Evaluate the result

After the query, a condition in the Flow checks whether the result set is empty:

length(body('Get_items')?['value']) is greater than 0

If true: The person is absent during the period. The Flow can then:

• Reject the assignment and notify the requester,
• suggest an alternative responsible person,
• create the task with a note about the absence and copy the substitute.

If incorrect: The person is available. The flow continues with the original action.

Use substitute from absence entry

If the absence list contains a "Substitute" column and it is filled, the flow can read the substitute from the absence entry and use it for the assignment. This avoids a manual search for the substitute and makes the process self-sustaining.

Define the approval workflow and status changes clearly

An absence request in SharePoint triggers an approval workflow when a new entry with the status "Requested" is created. Power Automate provides the action "Start and Wait for Approval" from the Approval connector.

Flow of a simple approval workflow

1. Trigger: New entry in the absence list with status "Requested".
2. Check for Overlap: Flow checks if an already approved entry for this person exists in the period.
3. If Conflict: Flow sets status to "Rejected", comment is set automatically, person is notified.
4. If No Conflict: Approval request is sent to the supervisor.
5. Approved: Status is set to "Approved", approval date and approver are entered, person and optionally substitute are notified.
6. Rejected: Status is set to "Rejected", rejection comment is entered, person is notified.

Who approves: supervisor or team lead

The approver must come from a reliable source. Three approaches are common:

• Fixed "Approver" field in the list: The requesting person enters their supervisor when creating the request. Simple, but error-prone if the field is left empty or filled incorrectly.
• Lookup from personnel list: A central personnel list in SharePoint contains the assigned leader for each person. The flow reads the approver automatically from this list.
• Azure AD / Entra ID Manager attribute: The flow uses the action "Get Office 365 User" and reads the Manager attribute from Entra ID. This attribute must be maintained in Entra ID and does not always match the actual approval structure in smaller companies.

The combination of a personnel list with a fallback to a manual approver field is a good compromise for most company sizes.

Timeout and manual escalation

Approval flows can be equipped with a timeout. If no decision is made after a defined time, the flow escalates automatically:

• Reminder email to the approver,
• after a further waiting period, notify the next higher supervisor or the HR team.

Handle conflicts, coverage, and notifications

Overlap conflict between multiple requests

When multiple people in a team apply for leave simultaneously and team coverage is an issue, a simple check at the individual level is insufficient. A team coverage check is then required.

One approach: In a separate team list, define per team the maximum number of people allowed to be absent simultaneously. Upon receipt of a new request, the flow checks how many approved absences for the same team already exist in the specified period. If the number exceeds the defined maximum, the request cannot be automatically approved and is marked for manual review.

Coverage notification

When an absence is approved, the registered coverage person should be automatically notified. The notification should include:

• Name of the absent person,
• Period of absence,
• Type of absence (vacation, business trip, etc.),
• optional notes on open tasks or ongoing processes.

A Teams message or a structured email is suitable for this notification. A structured Teams Adaptive Card containing the most important information is clearer for the coverage person than a free-text email.

Cancellation and withdrawal

An approved vacation can be canceled. For this, the status is set to "Canceled". The flow detects this status change and notifies approvers and coverage. Important: The status "Canceled" must not be equated with "Rejected". A canceled request was approved and was consciously withdrawn. A rejected request was never approved.

Typical errors with date fields, time zones, and duplicate entries

Time zone bugs in date comparisons

The most common error in absence checks is a time zone issue. SharePoint stores date fields internally in UTC. Power Automate processes date expressions also by default in UTC. If a user enters a date in their local time zone (e.g., Central European Summer Time, UTC+2), a shift of two hours can occur.

Concrete impact: A request with "From: 15.07.2024 00:00 CET" is stored in SharePoint as "14.07.2024 22:00 UTC". A filter checking for 15.07. may not find the entry.

Solution: Either consistently perform all date comparisons in UTC and ensure that the form passes date values in UTC, or use the convertTimeZone() function in Power Automate to convert all values to a uniform time zone before comparison.

Multiple entries for the same period

If a user submits multiple requests for overlapping periods, duplicate entries are created. The check at request entry—not at approval—prevents such overlaps from entering the workflow.

When creating a new request, the flow checks:

• Does this person already have a request with status "Requested" or "Approved" for this period?
• If yes: Reject the new request and inform the user.

Person field errors in queries

The OData filter on a person column requires the email address in UPN format, not the display name. If the flow retrieves the person from a form input or a previous action, ensure that the email address format is correct.

Typical error: The filter uses Person/Title eq 'Max Mustermann' instead of Person/EMail eq 'max.mustermann@domain.com'. The first format works unreliably in practice because display names are not unique.

How to keep absence checks reliable in daily operations

A stable absence and vacation process in SharePoint and Power Automate requires two main things: a clean data model with clear status values and filter logic that correctly handles time zones and overlaps. Both can be specifically tested in the development phase with a handful of test cases.

For data protection and permissions: Absence lists contain personal data. Access should be limited to individuals who need this data for their role. Approving personnel typically see all requests from their team, not those from other teams. Employees typically see only their own entries. The permission structure of the list and a possible row-level security via Power Apps or specific views should be defined early.

If absences are part of a larger context, for example as part of a complete personnel management process with Power Apps, the article Manage Attendance & Vacation Digitally with Power Apps describes the overarching process model.