Post

1 follower Follow
0
Avatar

Calculate the project quarter of a task

Hi,

 

I want to create a custom field that automatically calculates in which quarter of a project the task end date is. (They are different from the calendar quarters and every project might start on the 1st of any month)

 

I stumbled over this:

If(In(Month($Duedate),1,2,3),"Q1",If(In(Month($DueDate),4,5,6),"Q2",If(In(Month($DueDate),7,8,9),"Q3","Q4")))

Which is able to calculate in which Quarter the task is based on its own due date. I tried to modify it to find the difference between the parent project start date and the tasks due date in months but currently don't get anywhere. 

 

My main problem is how to get the parent project's start date into the formula.

I was thinking of something along the lines of:

If(In(Month($Duedate)-$ParentProject($StartDate),1,2,3),"Q1" ....

However, as $ParentProject is not a function this clearly doesn't work ... does anyone know how I could make it work?

thanks a lot!

 

 

 

 

Robin Head Answered

Please sign in to leave a comment.

1 comment
Date Votes

0
Avatar

I found the solution myself. 

I first setup a field that contains the month the task finished in, in respect to the month the project starts.

This requires a workflow rule as the field based on a formula can only use fields of the same object.

as the month are labeled 1 to 12, the workflow rule first as to check whether the duedate is in the same year as the project startdate:

 

"""

1. Conditional action list

if True =

Year($DueDate)-Year($ParentProject.StartDate) = 0

 

1.1 Update Field

$C_ProjectMonth

Month($DueDate)-Month($ParentProject.StartDate) +1

 

2. Conditional action list

If True = 

Year($DueDate)-Year($ParentProject.StartDate) = 1

2.1 Update Field

Month($DueDate)-Month($ParentProject.StartDate) + 1 +12

 

"""

This has to be continued for as many years your projects tend to tun. (I've added the +1 to make month 1 = 1 and not 0)

 

 

Following this workflow rule, I have created a custom field that calculates the Project quarter the task ends in. this can be done via a custom field that is based on a formula since all required pieces of information are within the same object.

(The Field is a picklist in my case so I can use it in reports for grouping in cluster graphs)

The field has as picklist entries: 

Q1, Q2 , Q3, Q4, Q5, Q6, Q7, Q8, Q9, Q10, Q11, Q12

and the formula to calculate the quarter is a conditional statement that groups the month into quarters:

If(In($C_ProjectMonth,1,2,3),"Q1",
If(In($C_ProjectMonth,4,5,6),"Q2",
If(In($C_ProjectMonth,7,8,9),"Q3",
If(In($C_ProjectMonth,10,11,12),"Q4",
If(In($C_ProjectMonth,13,14,15),"Q5",
If(In($C_ProjectMonth,16,17,18),"Q6",
If(In($C_ProjectMonth,19,20,21),"Q7",
If(In($C_ProjectMonth,22,23,24),"Q8",
If(In($C_ProjectMonth,25,26,27),"Q9",
If(In($C_ProjectMonth,28,29,30),"Q10",
If(In($C_ProjectMonth,31,32,33),"Q11",
If(In($C_ProjectMonth,34,35,36),"Q12",
""))))))))))))

 

Again this would need to be adapted depending on how long your projects run.

 

This obviously falls apart if a task starts and ends in different quarters, so tasks have to be split so they start and end in the same quarter. I have created further custom fields that essential do the same as above, but for the tasks start date and at the end a field that compares the project start quarter to the end quarter. If they are in separate quarters the quarter number is highlight to make the user aware that this task requires splitting.

 

I know this is a "workaround" rather than using the time-phased information, but this way I can use the baseline apps currently available.

 

Hope this might help someone

 

Robin Head 0 votes
Comment actions Permalink