Change Value of Yes/No based on Expiration Date

(0) Share

Report

Posted on by sugarfreeman

Hello,

I want to create a power automate flow that will let me know if an item in my list is expired (over 6 months)

I want to create a flow that would check the metadata of the List item "Created" and if it's older than 6 months, change the colour format of the item.

My thoughts is to have a Yes/No column that I will use a flow to change that from No to Yes after 6 months, that would then trigger a colour change based on rules inside the SharePoint List, i do not want to delete or change anything else, just the colour.

i hope this makes sense.

Please let me know if there's a better way to do this.

Thanks!

Sugar.

Categories:

General topics

I have the same question (0)

All responses (4)

Answers (1)

Verified answer

AWorley 414 Super User 2024 Season 1 on at

Like (1)

Report
Ideally you can do an expression that is on a reoccurrence schedule of 12 hours. Below is the arguments provided and the flow for you to validate:

Current Flow Date:
formatDateTime(utcNow(), 'dd/MM/yyyy')

6 Month argument(30.4 * 6 = 182.4. Round up an extra day to account for the lapse of greater than 6 months):
formatDateTime(addDays(items('Apply_to_each')?['Created'],-183),'dd/MM/yyyy')

Was this reply helpful? Yes No
sugarfreeman 8 on at

Like (0)

Report

it's not working - i think i did everything as you said. (also, shouldnt it be greater or equal?)

Was this reply helpful? Yes No
AWorley 414 Super User 2024 Season 1 on at

Like (1)

Report
No as you are comparing the created date (the past) to today's date and when they are equal to -183 days or 6 months + 1 day. It will register as True. You can do Greater than or equal to as the argument for any value greater than 6 months.

I.e. Today = 01/06/2023 -183 days = 30/11/2023

So the created date would have to equal November 30th to register true. Since you should run the flow on 12 hour time schedules this value should be caught.

Also I see in your new screen shot you are doing 2 apply to each values. 1 for "values" and 1 nested for "created". You should be able to remove the "Created" apply to each. The expression already extracts the created value from the apply to each.

formatDateTime(addDays(items('Apply_to_each')?['Created'],-183),'dd/MM/yyyy')

Was this reply helpful? Yes No
sugarfreeman 8 on at

Like (0)

Report

Hello Aworley,

I've gotten it to work but it only works for values exactly equal to 6months ago, as there are items in the list that were created longer than 6 months ago.
i changed them manually but i'd be interested to know how to fix this.

I've tried to change it to equal or greater but it doesn't seem to work.

cant thank you enough!

Was this reply helpful? Yes No