Unanswered

Calculating the difference between two dates and updating a sharepoint list item

(0) Share

Report

Posted on by JGmustang37

Disregard this post: I found a much easier solution, I just created a calculated column in sharepoint list. and I will just create a simple re-occurrence trigger to update those fields each day.

Hello all,

I am having trouble with a flow and updating a list item in my sharepoint list.

Goal:

Everyday at 5 am I need two columns updated in my SharePoint list.

1. TodayDate-I have this working using the utcNow() expression.

2. Eligibility- This is the one I am having issues with. What I am trying to do is compare a Date of Birth column with the TodayDate column, if the value is greater than or equal to 19 years, then I need to mark the column as Ineligible, if the value is <19, then mark as eligible.

I cannot get the first step to work properly, I am having a difficult time trying to calculate the years between the Date of Birth column and the TodayDate column. I tried using =DATEDIF(DOB,TodayDate,"Y") but it says the expression is invalid. If I simply type it in my update item action field, it updates all the items in the list as the actual formula, "=DATEDIF(DOB,TodayDate,"Y")".

Can anyone please help me out?

Categories:

Using flows

I have the same question (0)

All responses (2)

Answers (0)

Sort by

mcin9781 Microsoft Employee on at

Like (0)

Report
Copy link

Link copied!

Great, do you have an article that showed you how to do this?

Was this reply helpful? Yes No
JGmustang37 2 on at

Like (0)

Report
Copy link

Link copied!

I referred to the following site for basic syntax:
https://support.office.com/en-us/article/examples-of-common-formulas-in-sharepoint-lists-d81f5f21-2b4e-45ce-b170-bf7ebf6988b3

and just added what I knew from previous experience.

Final formula for my calculated column:

This checks the Date of birth column and compares it to the current date column:
=IF(DATEDIF(DOB,TodayDate,"Y")<=18,"eligible",IF(DATEDIF(DOB,TodayDate,"Y")>=19,"Ineligible"))

To automate the daily update of the TodayDate column, I created a recurrence>Get Item> Apply to each flow that updated my TodayDate column with the expression: utc(NOW().

This runs at 5:00 am every day and updates all items in the TodayDate column with the current date.

I then created another Recurrence>Send HTTP Request to SharePoint flow that reposts the calculated column each day at 5:05 and re-runs the calculated formula.

I used this article as a reference:https://us.flow.microsoft.com/manage/environments/Default-b945c813-dce6-41f8-8457-5a12c2fe15bf/flows/9e0c6353-7281-4fe7-9549-0b4ce2872b9b

Due to my company policies, I can not change my system's date and re-run the flow to see if this last flow works, but I will be able to check first thing in the morning as I have data setup to trigger the ineligibility result tomorrow.

Was this reply helpful? Yes No