Need help with Microsoft Project Custom formula for auto populating a custome text field

[kartnid]

Folks/Guru's, need some help on getting a custom formula driven auto text based on conditions. The logic I am trying to solve here is

1. Align Tasks based on a given month to align a custom field populate based on condition and reflect it..

Example: Want to lay out a release calendar for key tasks deemed as milestones (zero duration or milestone flag set to "YES" align to R1, R2, R3, R4 in a given calendar year.

Condition is

If Finish Date >= Jan 1st YYYY and < April 1st YYYY then the custom field should show "R1"

If Finish Date >= April 1st YYYY and < June 1st YYYY then the custom field should show "R2"

If Finish Date >= July 1st YYYY and < October 1st YYYY then the custom field should show "R3"

If Finish Date >= October 1st YYYY and <Jan 1st YYYY then the custom field should show "R4"

I need this help at the earliest and please help me out. thanks!

[#2]

@kartnid

Try this formula in a custom task Text field.

Text1=Switch(month([Finish])>=1 And month([Finish])<4 And day([Finish])>=1 And day([Finish])<31,"R1",month([Finish])>=4 And month([Finish])<7 And day([Finish])>=1 And day([Finish])<32,"R2",month([Finish])>=7 And month([Finish])<10 And day([Finish])>1 And day([Finish])<31,"R3",month([Finish])>=10,"R4")

I didn't check it out 100% but here is the result on a quick sample file

John

[#3]

@John tyvm. Will check it out and see if it working for me. Tyvm.

[#4]

@kartnid 

Okay, let me know. And if it answers your question, please consider marking my response as the answer.

John

[#5]

@John, first of all many thanks. I get few errors for few of the rows that have a finish date of 1st of the month as a pattern. Seem to be getting a #error for dates ending 7/1, 8/1, 9/1 for any year for that matter.

[#6]

@John Sorry hit send too early..it is happening also for dates ending on 31st too..so long story short all finish dates ending on 31st and 1st are erroring out.

[#7]

@kartnid 

Well I did say that I didn't fully check it out. A few tweaks to the formula should fix those issues. Try this formula:

Text1=Switch(month([Finish])>=1 And month([Finish])<4 And day([Finish])>=1 And day([Finish])<32,"R1",month([Finish])>=4 And month([Finish])<7 And day([Finish])>=1 And day([Finish])<32,"R2",month([Finish])>=7 And month([Finish])<10 And day([Finish])>=1 And day([Finish])<32,"R3",month([Finish])>=10,"R4")

John

[#8]

@John thanks and seems to be working fine. Now if I want to use this only for milestone tasks only do I have to enclose the complete switch statement within a condition of milestone = Y or duration = 0 and not show any R# if milestone = N

[#9]

kartnid,
You're welcome and thanks for the feedback.

And yes, that is correct, expand the formula with a conditional for milestones only. My approach would be to include a new statement as the first item in the Switch function that excludes non-milestones, something like this:
Text1=Switch([Duration]>0,"", month.....)

John
John