cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ConnellP
Frequent Visitor

14 Day reminders if there's no end date

Hi, 

I've got an absence log set up with  'Start Date' and 'End Date' columns. What I want to do is send an email to the person who created the log 14 days after the absence started, as a reminder to either 'end' the absence, or let HR know it's still ongoing. I know how to do this with the reminder function (-14) but I need to write something that means the email doesn't send if there's already an entry in the end date column. 

How can I do this? 

1 ACCEPTED SOLUTION

Accepted Solutions

Hello @ConnellP 

That sounds/looks very complicated 🙂

This is what I did in order to get items that started 14 days ago and has no end date (DueDate in my list).

Flow.FilterQuery.Dates.Null.pngMy dates are without time!

First expression is:

formatDateTime(addDays(utcNow(),-14),'yyyy-MM-dd')

Second expression is:

formatDateTime(addDays(utcNow(),-13),'yyyy-MM-dd')

 

That's it 🙂

Kind regards, John

View solution in original post

10 REPLIES 10
JohnAageAnderse
Memorable Member
Memorable Member

Hello @ConnellP 

 

This sounds like you need a recurrent flow that runs every day.

The flow then Gets all items for which the Start Date is equal to Today - 14 days and the End Date is empty (null).

Loop through the items and send a reminder to the creator.

 

What have you already tried?

Kind regards, John

Yep! That's exactly what I'm trying to do, at the moment I've got it set up to do the -14 day notice without any issue, this is what the formula looks like: concat(concat('DateStarted',' ge ', '''',outputs('Today_+_x_days')),'''',' and ', concat('DateStarted',' lt ','''', outputs('x_Days_+_1'),'''')) I'm just having a brain block on how to adjust that formula so it doesn't pull data that has an end date entered! The Absence End Date column is obviously in a different one to the start date, I'm just unsure how to formulate it. Thanks for your help!

Hello @ConnellP 

That sounds/looks very complicated 🙂

This is what I did in order to get items that started 14 days ago and has no end date (DueDate in my list).

Flow.FilterQuery.Dates.Null.pngMy dates are without time!

First expression is:

formatDateTime(addDays(utcNow(),-14),'yyyy-MM-dd')

Second expression is:

formatDateTime(addDays(utcNow(),-13),'yyyy-MM-dd')

 

That's it 🙂

Kind regards, John

Hi!

This was working great on Thursday, I took out the '13 days' formula as I didn't think I needed it and it was still working. 

Today it's decided it's going to send an email to blank Date Ended but it seems to be ignoring the -14 rule because it's sending notifications for 6th and 7th of April! 

example.png

Hello @ConnellP 

Yes, it will, which is why the -13 days was in there 🙂 The startdate must be 14 days in the past, not just 1-13 days in the past!

So put it back and it will work as a 14 day reminder. Kind regards, John

@JohnAageAnderse That's brilliant! Thank you so much!

Another question now, sorry! I'm now trying to track anything that was entered over 14 days from the current day (and has no end date). I've got a flow where it pulls the list into an Excel but I'm struggling to filter it out. 

The earliest entered start date is mid-February, so I attempted something like 'DateStarted ge ' 

formatDateTime(addDays(utcNow(),-50),'yyyy-MM-dd')' and DateEnded eq null. But I think I need to make the Date Started formula an open count? So anything past 14 days from today I need to know about, does that make sense?

Hello @ConnellP 

As you want to get items before 14 days ago, then the start date just need to be less than -14 days ago 🙂 and with end date empty.

Does this help?

Kind regards, John

Hi @JohnAageAnderse 🙂

I've written the formula as 

formatDateTime(addDays(utcNow(),-60),'yyyy-MM-dd') as that'll cover the earliest possible date entered, but it's still returning values from yesterday and I've no idea why! 
the 'eq null' is working fine but I'm just stumped as to why it's not working, I'm sure it's something completely daft that I'm missing

 

Hello @ConnellP 

You really don't need that formula 🙂 you only need this:

 

StartDate lt formatDateTime(addDays(utcNow(),-14),'yyyy-MM-dd') and EndDate eq null

 

That will give you all items from the start of time till 14 days ago, which has an empty EndDate.

Kind regards, John

Ah it works!! Thank you so much for your help! I was using ge instead of it, that's where I was going wrong! 

Thank you again 🙂

Helpful resources

Announcements
Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

New Ideas Forum MPA.jpg

A new place to submit your Ideas for Power Automate

Announcing a new way to share your feedback with the Power Automate Team.

MPA Virtual Workshop Carousel 768x460.png

Register for a Free Workshop

Learn to digitize and optimize business processes and connect all your applications to share data in real time.

MPA Licensing.jpg

Ask your licensing questions at the Power Automate AMA!

Join Priya Kodukula and the licensing team, super users and MVPs to find answers to your questions on Power Automate licensing.

Users online (4,353)