Skip to main content
3 of 3
added 542 characters in body
Matiur Rahman
  • 3.1k
  • 2
  • 8
  • 8

Keeping the current formula intact, we can replace [Quoted Days] with INT([Quoted Days]/People) assuming fraction to be rounded to the nearest whole number.

=[Start Date]+TRUNC((INT([Quoted Days]/People)-1)/5,0)*7+MOD((INT([Quoted Days]/People)-1),5)+IF(WEEKDAY([Start Date])+MOD((INT([Quoted Days]/People)-1),5)>6,2,0)-IF(WEEKDAY([Start Date])=7,1,0)-IF(AND(OR(WEEKDAY([Start Date])=7,WEEKDAY([Start Date])=1),MOD((INT([Quoted Days]/People)-1),5)=0),2,0) 

When [People]=1 should show the values for the original formula enter image description here

The updated formula will take effect when [People]>1 enter image description here


Updating to round the fraction to 1 decimal place. To see the effect of this change, [Start Date] and [Project End Date] should be formatted to [Date and Time].

The updated formula

=[Start Date]+TRUNC((ROUND([Quoted Days]/People,1)-1)/5,0)*7+MOD((ROUND([Quoted Days]/People,1)-1),5)+IF(WEEKDAY([Start Date])+MOD((ROUND([Quoted Days]/People,1)-1),5)>6,2,0)-IF(WEEKDAY([Start Date])=7,1,0)-IF(AND(OR(WEEKDAY([Start Date])=7,WEEKDAY([Start Date])=1),MOD((ROUND([Quoted Days]/People,1)-1),5)=0),2,0) 

enter image description here

Matiur Rahman
  • 3.1k
  • 2
  • 8
  • 8