cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
beckyjc
Level: Power Up

Comparing dates

Hi,

 

I'm just getting started with Flow and trying to compare a date from Microsoft Dynamics 365 with today's date to send an email if the date from CRM is not greater than or equal to today.

 

So far, I've got:

 

@greaterorequals
(
formatDateTime(item()?['abc_latesttimeentered'],'dd-MM-yyyy'),
formatDateTime(utcnow(),'dd-MM-yyyy')
)

 

This works fine for some records and not others. It seems that it works for those where the (UK format) abc_latesttimeentered date could potentially be misunderstood as US-format, e.g. 03/02/2018 but doesn't work for records with day numbers greater than 12, e.g. 17/03/2018. 

 

Where am I going wrong? 

 

Thanks,

Becky

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: Comparing dates

Hi @beckyjc,

 

Which Date format does the abc_latesttimeentered column return? Is it a UK format?

Could you please share a screenshot of your flow's configuration?

Further, could you please show a bit more about the result the abc_latesttimeentered column returned?

 

I think your thought is correct, the first parameter of formatDateTime() function is required to fill a datetime string which must match ISO 8601 format. The UK format datetime string could not match ISO 8601 format. If you convert the UK format datetime string into other format using formatDateTime() function, the error would show up as below:20.JPG

 

19.JPG

 

If the date time the abc_latesttimeentered column returned is a UK format (e.g. 17/03/2018), you could consider take a try to compare with today's date with the following workaround:

  • Add a proper trigger, here I use Flow Button trigger.
  • Add a Variables-> "Initialize variable" action, Name set to UK_format_datetimes, Type set to String and Value set to 17/03/2018
  • Add a Compose action, Inputs set to following formula:
split(variables('UK_format_datetimes'),'/')
  • Add a "Compose 2" action, Inputs set to following formula:
concat(outputs('Compose')[1],'/',outputs('Compose')[0],'/',outputs('Compose')[2])

Note: This action is used to convert the UK format datetime string (dd/MM/yyyy) into US format datetime string (MM/dd/yyyy).

 

  • Add a Condition, click "Edit in advanced mode", type the following formula:
@not(greaterOrEquals(outputs('Compose_2'), utcNow('MM/dd/yyyy')))

or

@less(outputs('Compose_2'), utcNow('MM/dd/yyyy'))
  • Within "If/yes" branch of Condition, add a "Send me an email notification" action.

Image reference:21.JPG

 

22.JPG

 

The flow works successfully as below:23.JPG

 

 

 

More details about ISO 8601 format, please check the following article:

ISO 8601 format

 

More details about using expression in flow actions, please check the following article:

Using expression in flow actions

 

 

Best regards,

Kris

 

 

Community Support Team _ Kris Dai
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

3 REPLIES 3
RodrigoF
Level: Powered On

Re: Comparing dates

beckyjc1
Level: Power Up

Re: Comparing dates

Hi Rodrigo,


Thanks for this, though I'm not sure using "ticks" will help in this particular situation. 

 

Would using "ticks" resolve the US vs UK date format issue that I appear to have? I seem to be seeing the correct results for some records and not others.  Am I correct in thinking that using ticks would bring the time component of the date/time into play?  I need to ignore the time in the comparison as am only interested in comparing the date. 

 

Appreciate any further help or guidance as I'm still stuck on this one.

 

Thanks,

Becky

 

 

Community Support Team
Community Support Team

Re: Comparing dates

Hi @beckyjc,

 

Which Date format does the abc_latesttimeentered column return? Is it a UK format?

Could you please share a screenshot of your flow's configuration?

Further, could you please show a bit more about the result the abc_latesttimeentered column returned?

 

I think your thought is correct, the first parameter of formatDateTime() function is required to fill a datetime string which must match ISO 8601 format. The UK format datetime string could not match ISO 8601 format. If you convert the UK format datetime string into other format using formatDateTime() function, the error would show up as below:20.JPG

 

19.JPG

 

If the date time the abc_latesttimeentered column returned is a UK format (e.g. 17/03/2018), you could consider take a try to compare with today's date with the following workaround:

  • Add a proper trigger, here I use Flow Button trigger.
  • Add a Variables-> "Initialize variable" action, Name set to UK_format_datetimes, Type set to String and Value set to 17/03/2018
  • Add a Compose action, Inputs set to following formula:
split(variables('UK_format_datetimes'),'/')
  • Add a "Compose 2" action, Inputs set to following formula:
concat(outputs('Compose')[1],'/',outputs('Compose')[0],'/',outputs('Compose')[2])

Note: This action is used to convert the UK format datetime string (dd/MM/yyyy) into US format datetime string (MM/dd/yyyy).

 

  • Add a Condition, click "Edit in advanced mode", type the following formula:
@not(greaterOrEquals(outputs('Compose_2'), utcNow('MM/dd/yyyy')))

or

@less(outputs('Compose_2'), utcNow('MM/dd/yyyy'))
  • Within "If/yes" branch of Condition, add a "Send me an email notification" action.

Image reference:21.JPG

 

22.JPG

 

The flow works successfully as below:23.JPG

 

 

 

More details about ISO 8601 format, please check the following article:

ISO 8601 format

 

More details about using expression in flow actions, please check the following article:

Using expression in flow actions

 

 

Best regards,

Kris

 

 

Community Support Team _ Kris Dai
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

Helpful resources

Announcements
thirdimage

Power Automate Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

firstImage

Incoming: New and improved badges!

We've given our badges an overhaul and also added some brand new ones!

fifthimage

Microsoft Learn

Learn how to build the business apps that you need.

sixthImage

Power Platform World Tour

Find out where you can attend!

seventhimage

Webinars & Video Gallery

Watch & learn from the Power Automate Community Video Gallery!

Top Kudoed Authors (Last 30 Days)
Users online (5,069)