Automatic Date Calculation not working

(2) Share

Report

Posted on by RP-08051828-0

Hello All,

This what I'm trying to make a flow out of. The youtube vids are not helping:

I am building a Power Automate flow to manage contract renewals using a SharePoint list. Each contract has a Contract End Date, and two numeric fields: Renewal Notice Days and Prereview Notice Days. I want the flow to calculate notification dates by subtracting those day values from the Contract End Date. If the current date matches the calculated Renewal or Prereview notification dates, I want to send an email reminder to the person listed in the "Email PM" column (a SharePoint Person field). I do not want to use calculated columns in SharePoint; instead, I want all date math and conditions handled inside the Power Automate flow.

Can anyone help?

updated with comment on 6/4/2025.

Categories:

Using flows

Capture.PNG

I have the same question (0)

All responses (6)

Answers (0)

Michael E. Gernaey 53,452 Super User 2025 Season 2 on at

Like (0)

Report

Hi @RP-08051828-0

Happy to help and want to acknowledge that I ask for things in ways maybe others do not. But you are sort of tossing stuff in without slowing down to :-) hehe break it apart for clarity and always share your flow, with a clear indication of "what" isn't working and where so we can help. :-) Just want you to get the most out of this as thousands posts and we want to help them all. Well I know for a fact Markus does :-) darn it my @'s aren't working.

Your subject says

Automatic Date Calculation not working

But I do not see anything related to calculated columns or flow actions or anything in this post.

Your Description

I am building a Power Automate flow to manage contract renewals using a SharePoint list.

Each contract has a Contract End Date, and two numeric fields: Renewal Notice Days and Prereview Notice Days.

Is a contract a line item in a list? is it part of multiple lists with lookups?

I want the flow to calculate notification dates by subtracting those day values from the Contract End Date.

Ok, I do see your two columns in the picture (thank you for that).

Now I want to stop there. Your request is about date calculations not working, so the assumption is we would fix it so your calculations aren't working, but the next part literally has nothing to do with that at all.

Need:

If the current date matches the calculated Renewal or Prereview notification dates, I want to send an email reminder to the person listed in the "Email PM" column (a SharePoint Person field).

I respect that you want this and have no issue, however, keep in mind that there is a better way, and also understand that if you have 10000000000 contract lines (and I truly hope you are successful and have that!!!! you haven't made a way for the flow to NOT loop through ones that you already contacted on). Meaning you are going to loop on every row for the end of time.

I do not want to use calculated columns in SharePoint; instead, I want all date math and conditions handled inside the Power Automate flow.

Suggestion: My suggestion is a different type of solution altogether.

You add 2 columns. Make them actual Date only Columns.

Now you have

Contract Start/End both dates

Renewal Days / Reminder days both numbers

I do not know when the Renewal Days and Reminder Days #'s are added, but you should either

a) have whatever puts the data in, calculate and store in the new date columns

b) have a power automate automated flow that when these # columns are populated/changed, they update the 2 new Columns

This way, on a daily basis, you only need to run a Filtered query where EITHER of those new dates (only) are either to today's date.

Instead of looping through, calculating each time and going oh ok yeap that one its a much more complex process.

Was this reply helpful? Yes No
lbendlin 8,479 Super User 2025 Season 2 on at

Like (0)

Report
I want all date math and conditions handled inside the Power Automate flow

Read about ISO-8601 format, and about the date math functions in Power Automate. Provide some concrete examples if you like more help.

Was this reply helpful? Yes No
RP-08051828-0 8 on at

Like (0)

Report
Hi guys, sorry for late reply:

compose (for both):

convertFromUtc(utcNow(),'Eastern Standard Time','MM-dd')

condition:

formatDateTime(items('Apply_to_each')?['Pre_x002d_Review_x0020_Date_x002'],'MM-dd') = output from Compose

I watched this video to get the flow I created: https://www.youtube.com/watch?v=asZHZwyvnaA

Here's more context:

Here's what I have:

I have a "Contract End Date" column.

I also have a "Pre-Review Period (Days)" column where I enter how many days before the contract ends I want to be notified (e.g., 3, 30, whatever number I type.).

Based on that, my flow does this:

Dynamically calculate a new date by subtracting the number of days in the "Pre-Review Period (Days)" from the "Contract End Date".

Updates a third column, "Pre-Review Date FYI", with that calculated date.

This is what I need based on my current flow setup:

Compare that calculated date to today's date, and if they match, send me an email reminder, which didn’t happen on test flow even though my test column has today’s date. \

Where I'm stuck:

I’ve seen examples where flows are triggered based on fixed periods like 30, 60, or 90 days — but I want this to be flexible, based on the number of days I enter in the "Pre-Review Period (Days)" column.

I think the response supplied by Tom here may be appicable, but idk how to modify my expressions/flow: https://community.powerplatform.com/forums/thread/details/?threadid=2362274c-8844-4150-951e-92bdfe0f5140

And I haven't been able to successfully receive a test email!! This is all new to me, but I know this is simple lol. Also, this is for tracking of 50 contracts.

flow- output.PNG

flow- image 1.PNG

list columns.PNG

Was this reply helpful? Yes No
David_MA 12,982 Super User 2025 Season 2 on at

Like (0)

Report
I created a manually triggered flow, which you can duplicate to see if it is what you are looking for. Try this:

Manually triggered flow with two parameters:

Date field for Contract End Date

Number field for either of the days fields.

Add a compose action with this expression: addDays(triggerBody()?['date'],int(concat('-', triggerBody()?['number'])))

The flow looks like this:

I ran it and entered July 4, 2025, for the date and enter 10 for the notice days. The result:

Is that what you are trying to do?

Was this reply helpful? Yes No
RP-08051828-0 8 on at

Like (0)

Report

@David_MA No, please review the comment I made one 6/4/2025.

Was this reply helpful? Yes No
David_MA 12,982 Super User 2025 Season 2 on at

Like (0)

Report
I don't think I am really following what you posted on 6/4, but it sounds like if today's date is equal to some date in a calculated column (which you have figured out how to calculate), then you want to send an e-mail. If so, and I am not sure it will work on a calculated date column but on an actual date column (it notes this as well in the link you referenced), do this:

Create a scheduled flow that runs at the time of day you want the notification to go out.

Use a get items action with a filter query to get the items where the calculated date ge startOfDay(utcNOW()) and calculated date is lt addDays(startOfDay(utcNOW()),1)

Use a condition with the expression length() on the value of the get items action to check if it is greater than 0 (meaning there are items today where a reminder needs to be sent).

On the yes side of the condition, add an apply to each on the items from the Get items action and send the e-mail.

Was this reply helpful? Yes No