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
Advocate V

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...

Advocate II

@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? 

Advocate V

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.

Advocate II

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

Power Automate
Status changed to: Under Review

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

Regular Visitor

Would love to have a function to convert to Excel serial date value. I have need to look up a row in Excel, created by Microsoft Forms. Excel changes submitted Date/Time to an Excel serial date value. Trying to then get the Excel row by that timestamp (the only unique key) is **bleep** near impossible, since it's already converted to a serial number in the Forms dumping ground spreadsheet.