“Never Fails Friday”: A Bullet-Proof Power Automate Flow that Posts Next Week’s PTO to Teams

If your flow keeps throwing template errors, never evaluates your Condition to true, or Power Automate insists that an action name “is not defined,” this post is for you. Below is a rock-solid, beginner-friendly build that lists employees whose First Day Out falls next week (Mon–Sun) and posts an Adaptive Card to Teams—without fragile expressions or time-zone gotchas.

---

What you’ll build

Every Friday at 8:00 AM Eastern, your flow will:

1. Read a SharePoint list

   • Employee (Person – single)

   • First Day Out (Date)

2. Normalize each item’s date into Eastern Time (ET)

3. Filter for next week (Mon–Sun)

4. Post a clean bulleted list to Teams as an Adaptive Card

---

Visual flow map (copy this shape)

Weekly First Day Out Reminder
│
├─ Recurrence (Fri 08:00 ET)
│
├─ Initialize: varNowET (String)
├─ Initialize: varDaysUntilNextMonday (Integer)
├─ Initialize: varNextMondayET (String)
├─ Initialize: varNextSundayET (String)
├─ Initialize: varNextMondayKey (String, yyyy-MM-dd)
├─ Initialize: varNextSundayKey (String, yyyy-MM-dd)
├─ Initialize: varLines (Array = [])
│
├─ SharePoint: Get items  (Expand Query: Employee)
│
└─ Apply to each: value
   ├─ Compose: FDO_Raw              ← First Day Out (token only)
   ├─ Convert time zone: ToET       ← UTC → Eastern; ISO output
   ├─ Compose: ConvertedET          ← store “Converted time” (token only)
   ├─ Compose: ItemDateKey          ← yyyy-MM-dd from ConvertedET   ✅
   ├─ Condition #1 (AND; 2 rows)    ← not blank AND ≥ varNextMondayKey
   │   └─ If Yes →
   │       ├─ Condition #2 (1 row)  ← ≤ varNextSundayKey
   │       │   └─ If Yes →
   │       │       ├─ Compose: MakeLine
   │       │       └─ Append to array: varLines
   │       └─ If No → skip
   └─ If No → skip
│
├─ Compose: ItemsMarkdown
└─ Teams: Post adaptive card and wait for a response  ← use the step’s internal name in JSON

---

Step-by-step (beginner friendly, zero guesswork)

1) Create the scheduled flow

• Create → Scheduled cloud flow

• Name: Weekly First Day Out Reminder

• Recurrence: Week → every 1 → Friday → 08:00

• Time zone: (UTC-05:00) Eastern Time (US & Canada)

---

2) Helper variables (paste in the Expression tab — no @{})

1. varNowET (String)
   convertTimeZone(utcNow(),'UTC','Eastern Standard Time')

2. varDaysUntilNextMonday (Integer)
   if(equals(dayOfWeek(variables('varNowET')),0),1,sub(8,dayOfWeek(variables('varNowET'))))

3. varNextMondayET (String)
   startOfDay(addDays(variables('varNowET'),variables('varDaysUntilNextMonday')))

4. varNextSundayET (String)
   startOfDay(addDays(variables('varNextMondayET'),6))

5. varNextMondayKey (String)
   formatDateTime(variables('varNextMondayET'),'yyyy-MM-dd')

6. varNextSundayKey (String)
   formatDateTime(variables('varNextSundayET'),'yyyy-MM-dd')

7. varLines (Array)
   []

Why these work: we compute next Monday/Sunday once in ET, then compare date-only strings—no DST/UTC surprises.

---

3) Read SharePoint

• SharePoint → Get items

  • Site Address: your site

  • List Name: your list

  • Advanced → Expand Query: Employee (so Employee/DisplayName is available)

---

4) Process each item

Apply to each → value from Get items

4.1 Compose — FDO_Raw

• Inputs: click Dynamic content → First Day Out (purple token only).

4.2 Convert time zone — ToET (exact settings)

• Action: Date Time → Convert time zone

• Base time: Outputs of FDO_Raw

• Source time zone (full name): Coordinated Universal Time (UTC)

• Destination time zone (full name): Eastern Time (US & Canada)
  (Windows ID: Eastern Standard Time — this auto-handles DST; do not pick “Eastern Daylight Time”)

• Format string (full name): Round-trip date/time pattern (ISO 8601)
  (the .NET o pattern)
  Example output: 2025-08-25T00:00:00.0000000-04:00

4.3 Compose — ConvertedET

• Inputs: insert Converted time (token) from ToET.
  (This gives us a stable step to reference.)

4.4 Compose — ItemDateKey ✅ (highlighted correction)

• Expression:

formatDateTime(outputs('ConvertedET'),'yyyy-MM-dd')

• Result: YYYY-MM-DD in ET for the current row.

---

5) Filter with two simple Conditions (Basic mode only)

Condition #1 — AND (two rows)

• Row 1 — Not blank
  Left: Outputs → ItemDateKey (token)
  Operator: is not equal to
  Right: (leave blank)

• Row 2 — On/after next Monday
  Left: Outputs → ItemDateKey
  Operator: is greater than or equal to
  Right: variable varNextMondayKey

If No: skip.
If Yes: continue.

Condition #2 — (one row)

• Row 1 — On/before next Sunday
  Left: Outputs → ItemDateKey
  Operator: is less than or equal to
  Right: variable varNextSundayKey

If No: skip.
If Yes: create the line and store it.

---

6) Build each line of text

Compose — MakeLine (Expression)

concat(
  '- ',
  coalesce(item()?['Employee']?['DisplayName'],'Unknown'),
  ' — ',
  outputs('ItemDateKey')
)

Append to array variable

• Name: varLines

• Value: Outputs of MakeLine (purple token only)

---

7) Join the lines

Compose — ItemsMarkdown (Expression)

if(
  equals(length(variables('varLines')),0),
  'No first day out next week.',
  join(variables('varLines'), '\n')
)

---

8) Post the Adaptive Card to Teams (the internal name trick)

Power Automate card JSON must reference the internal name of a step, not the label you see.

1. Open your ItemsMarkdown compose → ⋯ → Peek code
   Copy the value of "name" (example: Compose_3).

2. In Post adaptive card and wait for a response → Message → Code view, paste:

{
  "$schema": "http://adaptivecards.io/schemas/adaptive-card.json",
  "type": "AdaptiveCard",
  "version": "1.5",
  "body": [
    {
      "type": "TextBlock",
      "text": "Upcoming First Day Out (Next Week)",
      "weight": "Bolder",
      "size": "Large",
      "wrap": true
    },
    {
      "type": "TextBlock",
      "spacing": "Small",
      "wrap": true,
      "text": "**Window:** @{concat(variables('varNextMondayKey'),' – ',variables('varNextSundayKey'),' (ET)')}"
    },
    {
      "type": "TextBlock",
      "wrap": true,
      "text": "@{outputs('Compose_3')}"
    }
  ]
}

Replace Compose_3 with your internal name.
Use @{…} only inside this JSON; everywhere else use the Expression tab or tokens.

---

Test checklist (what to look for)

• ConvertedET → Outputs shows an ISO timestamp with -04:00 or -05:00.

• ItemDateKey → Outputs is YYYY-MM-DD.

• Condition #1 is true when the date is present and ≥ next Monday.

• Condition #2 is true when the date ≤ next Sunday.

• Teams card renders and shows either the bullet list or “No first day out next week.”

---

Why this never flakes out

• We capture the Convert step’s Converted time into our own ConvertedET compose, then build ItemDateKey from that. No more brittle outputs('ToET') references to display names.

• Filtering uses strings on both sides (ItemDateKey vs varNext*Key)—no mixed types, no template errors.

• The week window is computed once in ET and compared as date-only keys, so UTC/DST doesn’t bite you.

---