Hello @R_Pat ,
You could create the Calculated column in your List to count the number of days excluding weekends:
Go to the specific List >> click on Create Column on the ribbon >> select Calculated type to create a column named "Days“ >> On the Formula section, Please use the following command:
=IF(ISERROR(DATEDIF(StartDate,EndDate,"d")),"",(DATEDIF(StartDate,EndDate,"d"))-INT(DATEDIF(StartDate,EndDate,"d")/7)*2-IF((WEEKDAY(EndDate)-WEEKDAY(StartDate))<0,2,0)-IF(OR(AND(WEEKDAY(EndDate)=7,WEEKDAY(StartDate)=7),AND(WEEKDAY(EndDate)=1,WEEKDAY(StartDate)=1)),1,0)-IF(AND(WEEKDAY(StartDate)=1,(WEEKDAY(EndDate)-WEEKDAY(StartDate))>0),1,0)-IF(AND(NOT(WEEKDAY(StartDate)=7),WEEKDAY(EndDate)=7),1,0)+IF(HOUR(EndDate)<HOUR(StartDate),0,IF(TEXT(EndDate-StartDate,"h")>=4,0.5,1)))
After performing the above operations, you will find that the SharePoint List will automatically calculate the number of weekdays
Thanks,
Echo Du
…………………………………………………………………… Updated Answer …………………………………………………………………
Hi @R_Pat ,
I have modified Column Formula:
=IF(ISERROR(DATEDIF([Start Date],[End Date],"d")),"",(DATEDIF([Start Date],[End Date],"d"))-INT(DATEDIF([Start Date],[End Date],"d")/7)*2-IF((WEEKDAY([End Date])-WEEKDAY([Start Date]))<0,2,0)-IF(OR(AND(WEEKDAY([End Date])=7,WEEKDAY([Start Date])=7),AND(WEEKDAY([End Date])=1,WEEKDAY([Start Date])=1)),1,0)-IF(AND(WEEKDAY([Start Date])=1,(WEEKDAY([End Date])-WEEKDAY([Start Date]))>0),1,0)-IF(AND(NOT(WEEKDAY([Start Date])=7),WEEKDAY([End Date])=7),1,0)+IF(HOUR([End Date])<=HOUR([Start Date]),0,IF(TEXT([End Date]-[Start Date],"h")>=4,0.5,1)))
It should be noted that if the [Start Date] is later than the [End Date], the calculation result will not appear. Take Text6 as an example in your screenshot.
Thanks,
Echo Du
……………………………………………………………………… Updated Answer
……………………………………………………………
Hi @R_Pat ,
Does the following statement meet the meaning you want to say ?
When you enter the Date&Time value in the List, if you do not specify a specific time point, SharePoint online will automatically default to 12am.
When you specify a specific time point, if the difference of more than 4 hrs on the same day is counted as 1 day, if the difference is less than or equal to 4 hrs on the same day, it is counted as 0.5 day.
If yes, you can refer to the following formula:
=IF(ISERROR(DATEDIF([Start Date],[End Date],"d")),"",(DATEDIF([Start Date],[End Date],"d"))-INT(DATEDIF([Start Date],[End Date],"d")/7)*2-IF((WEEKDAY([End Date])-WEEKDAY([Start Date]))<0,2,0)-IF(OR(AND(WEEKDAY([End Date])=7,WEEKDAY([Start Date])=7),AND(WEEKDAY([End Date])=1,WEEKDAY([Start Date])=1)),1,0)-IF(AND(WEEKDAY([Start Date])=1,(WEEKDAY([End Date])-WEEKDAY([Start Date]))>0),1,0)-IF(AND(NOT(WEEKDAY([Start Date])=7),WEEKDAY([End Date])=7),1,0)+IF(HOUR([End Date])<=HOUR([Start Date]),0,IF(INT(TEXT([End Date]-[Start Date],"h"))>4,1,0.5)))
Effect picture:
Thanks,
Echo Du
==================
If an Answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.