ROUND((TOTALHOURS([End Date]-[Start Date])/24)+1)
This portion calculates the difference between the End Date and Start Date in hours and then divides it by 24 to get the total number of days. It rounds the result and adds 1 to consider the starting day.
ROUND((TOTALHOURS(WORKDAY([Start Date],ROUND((TOTALHOURS([End Date]-[Start Date])/24)+1))-[Start Date])/24)+1)
This part calculates the number of working days between the Start Date and the projected end day (after considering the total days calculated in part 1). The WORKDAY function returns a date by adding a number of workdays to the Start Date. Non-weekend days are generally considered workdays, but note that this doesn’t account for holidays.
This part adjusts the total days based on whether the End Date and the calculated workday fall within the same week or not:
IFS(
WEEKNUM([End Date])=WEEKNUM(WORKDAY([Start Date],ROUND((TOTALHOURS([End Date]-[Start Date])/24)+1))),1,
WEEKNUM([End Date])<WEEKNUM(WORKDAY([Start Date],ROUND((TOTALHOURS([End Date]-[Start Date])/24)+1))),3
)
- The first condition checks if the
End Dateand the projected workday fall in the same week (WEEKNUM). If true, it subtracts 1 from the result. - The second condition checks if the
End Dateis before the week of the projected workday. If true, it subtracts 3. This might be an adjustment for the weekend, assuming a typical workweek from Monday to Friday.
- ROUND((TOTALHOURS([End Date]-[Start Date])/24)+1)
This is subtracting the total number of days (including non-working days) calculated in Part 1 from the previous results.
