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

View solution in original post

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
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.

Users online (1,948)