# Calculated Column - Dynamic project end date

I am building a project list that has the following columns:

The "Projected End Date" column is calculated by taking the "Start Date" and adding the number of of "Quoted Days" (removing weekends) using the following formula:

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

This works great but I now need to factor in how many people are assigned to the project and then calculate the "Project End Date" from this.

Thus what I am trying to do is:

"Quoted Days" divided by "People" then add this number to the "Start Date" to give me the "Projected End Date". I could create another calculated column just to do the "Quoted Days divided by People" calculation and update the above formula to use the new calculated column but I wondered if there was a neater way to do this by tweaking the formula to do this calculation as well?

Rob

• How are you planning to handle if [Quoted Days]/[People] is not a whole number, like 135/2=67.5 ? Round it to the nearest whole number? Oct 27, 2020 at 18:50

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

The updated formula will take effect when [People]>1

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)

``````

• Sorry the late reply Matiur, thanks for the help. I will need the number to have 1 decimal point unfortunately, just to make it a bit more complicated. Oct 29, 2020 at 9:24
• You mean for [Quoted Days]/[People] like135/2=67.5, right? Sure, I will take a look into how date function would behave with the decimal numbers. Oct 29, 2020 at 12:07
• See the updated formula that I've posted. Let me know if you have any questions. If this answers your questions, mark it as answered Oct 29, 2020 at 17:49
• Perfect, thanks Matiur Oct 30, 2020 at 14:08