0

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

enter image description here

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?

Any thoughts gratefully received.

Rob

Improve this question
1
  • 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?
    – Matiur Rahman
    Commented Oct 27, 2020 at 18:50

1 Answer 1

Reset to default
1

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

Improve this answer
4
  • 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.
    – RobC_CTL
    Commented 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.
    – Matiur Rahman
    Commented 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
    – Matiur Rahman
    Commented Oct 29, 2020 at 17:49
  • Perfect, thanks Matiur
    – RobC_CTL
    Commented Oct 30, 2020 at 14:08

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.