Power Platform Community Forum Thread Details

Hi,

A question i can't seem to figure out.

My final result should be the number of workdays between the created data in my SP list and today without using a power automate flow that goes through each record. So, it should update every day without triggering my flow that automatically sends emails on a change...

I have tried different methods, the number of actual days using json (JSON link). By adapting that code I created a json format that would give me the serial date number of today (aparently calculated from 1-1-1970) so i managed to derive the actual serial date number of today from that. These are all in a 'single line of text' calculated column.

How can i now use this number as a date to calculate the number of working days between 2 columns?

I have a 'DaysToday' column containing the following json code:

{
"$schema": "https://developer.microsoft.com/json-schemas/sp/column-formatting.schema.json",
"elmType": "div",
"txtContent": "=floor((Number(@now))/(1000*60*60*24))"
}

I have a 'DateToday' column containing the following calculated formula:

=DATEVALUE("01-01-1970")+VALUE(DaysToday+0)

I want to use the DateToday column as a date so i can calculate the number of workdays between 2 columns with the following formula (at the moment it shows the difference between Created and Modified):

=IF(ISERROR(DATEDIF(Created,Modified,"d")),"",(DATEDIF(Created,Modified,"d"))+1-INT(DATEDIF(Created,Modified,"d")/7)*2-IF((WEEKDAY(Modified)-WEEKDAY(Created))<0,2,0)-IF(OR(AND(WEEKDAY(Modified)=7,WEEKDAY(Created)=7),AND(WEEKDAY(Modified)=1,WEEKDAY(Created)=1)),1,0)-IF(AND(WEEKDAY(Created)=1,(WEEKDAY(Modified)-WEEKDAY(Created))>0),1,0)-IF(AND(NOT(WEEKDAY(Created)=7),WEEKDAY(Modified)=7),1,0))

Any help is greatly appreciated!

Thanks!

Categories:

Building Power Apps