cancel
Showing results for 
Search instead for 
Did you mean: 

Add Date comparison functions

 

Really need to be able to compare dates and return the number of days (work days).

 

I've searched the forum and every question where this type of a request has been asked the answer is that you can’t do it – but this is a pretty basic need!

 

Example of need:

SharePoint List, has a Created Date and a last Modified Date.

I have a daily email summary that pulls from multiple lists the items that are still pending review by our supervisors and managers and sends one email with multiple lists to the group in the body.

Currently I am reporting on the date last modified but it would be much more impactful to list the number of (work)days since the record was last modified.

 

 

Please let us know if this is something that has been slotted for development or if not, I would please ask that you seriously consider adding it.

 

Thank you!

Status: Under Review

This would be a very valuable addition, thank you for the suggestion.

Comments
Level 8

I posted a workaround to datediff (all days, not just workdays) here: https://powerusers.microsoft.com/t5/Flow-Ideas/workflow-definition-language-needs-to-be-expanded/idc...

Level: Powered On

@degvalentine - interesting, if I am understanding you correctly it does not solve the issue raised in this post of needing to determine the number of work days, is that right? 

Level 8

Correct - my workaround was for all days, not just work days. But you might extend it (painfully) like so:

 

GIVEN

  {startDate}
  {endDate}

WHERE

  less(
    startOfDay({startDate}),
    startOfDay({endDate})
  )

SET {diffAllDays}
TO  int(
      div(
        sub(
          ticks(startOfDay({endDate})),
          ticks(startOfDay({startDate}))
        ),
        864000000000
      )
    )

SET {diffWorkDays}
TO  sub(
      sub(
        {diffAllDays},
        mul( // subtract 2 days for each full week
          int(
            div(
              {diffAllDays},
              7
            )
          ),
          2
        )
      ),
      if( // handle cases within 1 week
        less(
          dayOfWeek({startDate}),
          dayOfWeek({endDate})
        ),
        add(
          if(
            equals(
              dayOfWeek({startDate}),
              0
            ),
            1,
            0
          ),
          if(
            equals(
              dayOfWeek({endDate}),
              6
            ),
            1,
            0
          ),
        ),
        2 // if start dow > end dow then we had a weekend
      )
    )

So just insert your variables or outputs in place of the bracketted placeholders, and paste that monstrosity into your 300px by 20px expression box. Cat LOL

 

My workaround is inclusive of both the start and end dates. Also, I haven't tested this modification, so proceed with caution.

Level: Powered On

@degvalentine - Great thanks I'll give that a try

Power Automate Staff
Status changed to: Under Review

This would be a very valuable addition, thank you for the suggestion.