Comparing 3 dates for the latest in Power AUtomate

[WHSBPjM]

I have a case where I need to do the exact same thing as this post Compare multiple date/time columns and find the latest date, but utilize this in a SharePoint List through Power Automate. 

Here are my columns in SharePoint where the 'Current Target' is automated to reflect the latest of the other three target/commitment dates (that are manually entered). 

Examples:

12/1/22       empty/null       12/15/22            12/15/22

12/1/22       12/20/22          12/15/22            12/20/22   

I've developed a proof of concept (based on an online search) using Initizilize Variables, max(), and Compose. However, the only issue with this solution is max() only allows Integers or Decimals and not dates (String). 'The template language function 'max' expects all of its parameters to be either integer or decimal numbers. Found invalid parameter types: 'String'

[Rhiassuring]

Hi there, 

I'd recommend translating each of your dates to "ticks" (https://manueltgomes.com/reference/powerautomate-function-reference/power-automate-ticks-function/ ) and then you can compare easily that way. 

There are some good articles on this usage, and lots of forum posts that have similar solutions:

https://sharepains.com/2018/11/02/compare-dates-using-conditions/

https://debajmecrm.com/how-to-compare-dates-in-power-automate/

https://powerusers.microsoft.com/t5/Building-Flows/Compare-dates/td-p/54802

[v-dezhili-msft]

Hi @WHSBPjM 

Do you want to get the latest of the three dates?
You can first convert the date to an int type, use Max() to get their maximum value, and then convert it back to the date format.

formatDateTime(formatNumber(max(int(replace(variables('Maxdate'),'/','')),int(replace(variables('middledate'),'/','')),int(replace(variables('middledate'),'/',''))),'00/00/0000'),'MM/dd/yyyy')

Best Regards,

Dezhi

[WHSBPjM]

Hi @v-dezhili-msft ,

Yes, that is exactly what I am looking for. I've been thinking that would be the best way to do it, but I wasn't sure how that would be possible since I'm not very versed in coming up with these expressions.

I just tried to implement your recommended solution which works flawlessly when all three columns (Initial, ETS, ENG) are populated. Although I found out during testing, this expression would fail with the following error when there is no date in any field. Is there a way around this, if there is a null or empty field in these specific columns? 

Unable to process template language expressions in action 'Compose_-_Max_with_Date_Conversions' inputs at line '0' and column '0': 'The template language function 'int' was invoked with a parameter that is not valid. The value cannot be converted to the target type.'.

[WHSBPjM]

@v-dezhili-msft 

How do I include in the expression to treat the error when there is no date in any of the fields?

I believe there is a null or empty field in these specific columns. 

[SamLed]

Hi WHSBPjM,

Use such kind of expression :

addSeconds('0001-01-01', div(max(if(empty(body('Field1')), 0, ticks(body('Field1'))), if(empty(body('Field2')), 0, ticks(body('Field2'))),if(empty(body('Field3')), 0, ticks(body('Field3')))),10000000), 'MM/dd/yyyy') 

It checks for every field if date value is empty, returns date value converted to ticks if not, else returns 0.

if(empty(body('Field')), 0, ticks(body('Field')))

It finally convert max ticks value back to date format.

addSeconds('0001-01-01', div([Ticks value],10000000), 'MM/dd/yyyy') 

[Edit] You can also, in a further action, check if the output is "0001/01/01" that means all fields are empty. use ticks function to check, ticks('0001/01/01') equals 0.

If I have answered your question, please Accept the post as solution.
If you like my response, please Thumbs Up.