Problems with flow for calculated date columns that are expired updating choice pill status column

[leannewidge]

I am trying to build a flow for calculated date columns that are expired that would then update a choice pill status column with the correct status. I have tried doing it using a recurrence > get items > condition > if calc column is less than utcnow > update item, but when I run the flow it is somehow seeing the calc date column as MM/dd/yyyy and updating ones that are not expired. I seem to be going around in circles somewhere as the output from get items is showing the correct format of dd/MM/yyyy (see screenshots) but the condition is not working. Any ideas on fixing this would be greatly appreciated?

What I am trying to achieve:

A flow (or two if need be) to check daily that if "Calc6Mnth" is prior to todays date but "Calc2Yrs" is after todays date then update status to "Active, requires monitoring". If both are prior to todays date then update status to "Not active".

 

 

 

[ManishSolanki]

Hi @leannewidge 

Pls try using the below expression:

formatDateTime(utcNow(),'dd/MM/yyyy')

If this helps & solves your problem, please remember to give a 👍 and accept my solution as it will help others in the future.

[leannewidge]

@ManishSolanki thank you for your reply. I have tried changing it to that format and it still updates the non-expired ones as it is seeing the dates MM/dd/yyyy.

[ManishSolanki]

Pls try ISO format:

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

If this helps & solves your problem, please remember to give a 👍 and accept my solution as it will help others in the future.

[leannewidge]

@ManishSolanki that changed even more of the wrong ones and even changed one of the rows dates to 1899??

What I don't understand is how it is taking the year 2026 as being "less than" utcnow for only some of them and not all of them. For example in the screenshot below you will see it's changed 05/03/2026 (either 5th March or 3rd May whichever way it wants to read it) but 25/03/2026 is left unchanged, as is 26/02/2026.......is this because it can't convert that to be an alternative option of 25th/ 26th month and therefore it just skips it?

Besides that...it still should not be changing those ones that are the year 2026 as that is not less than utcnow?

 

[Andrew_J]

@leannewidge 

Okay I am working on the maths side of things.  Somewhere you need to do a test on the 6 months first. 

LHS	CENT	RHS
6 Month	le	formatdatetime(adddays(utcnow(), -185, 'yyyy-MM-dd'), 'dd-MM-yyyy')

Update on the Yes branch for the ACTIVE

on the Yes branch we need another if statement again the following:

LHS	CENT	RHS
2 years	ge	formatdatetime(adddays(utcnow(), 732, 'yyyy-MM-dd'), 'dd-MM-yyyy')

update on the Yes to read Not Active.

I hope that helps,

Andrew

[leannewidge · Accepted solution]

@Andrew_J @ManishSolanki thank you both for your suggestions. After going around in circles I have finally found a solution and have a working flow! The issues were arising from the calculated columns and everything I tried was just not working.

When I built my SP list I had the data feed automatically in to an excel sheet/table too. So I have amended the flow to get the data from the excel table instead. That was not without its issues too as excel stores dates as serial numbers 🙄.

Anyway, a few conditions and expressions later I now have a working flow! I have included screenshots below for anyone else having the same issues! 😃