Share
Report
44Hi,
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!
All responses (
Answers (
