Sign Up ›

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
econ242econ242 

Custom close date on Opportunity

Hi,

I need assitance on a formula for creating a default close date on Opportunities for existing Accounts. As we know, when a Lead is converted and an Opportunity is created, the default close date on that Opportunity defaults to the end of the current fiscal quarter from the day it was converted/created. I'd like to replicate that same functionality on existing Accounts so if a new Opportunity is created for an existing Account on July 3, 2016, the close date will update when the record is saved to September 30, 2016...the last day of the current fiscal quarter from which the Opportunity (create date) was created.

I already know how to custumize a close date for (x) amount of days i.e., create date +30, or +60 etc...but how can I get it to default to the last day of the quarter? Any help would be GREATLY appreciated!!!

Thanks so much!!!
Best Answer chosen by econ242
Parker EdelmannParker Edelmann
Hello @econ242,

I don't know the days of your fiscal quarters, so I'll use December 31st, March 31st, June 30th, and September 30th instead in the below formula. 
DATE(
     YEAR(TODAY), 
     CASE(MONTH(TODAY),1,3,
                       2,3,
                       3,3,
                       4,6,
                       5,6,
                       6,6,
                       7,9,
                       8,9,
                       9,9,
                       10,12,
                       11,12,
                       12,12, null),
     CASE(MONTH(TODAY),1,31, 2,31, 3,31, 4,30, 5,30, 6,30, 7,30, 8,30, 9,30, 10,31, 11,31, 12,31, null)
     )

This will give you the last day of the current calendar quarter.

I hope this helps.

Thanks,
Parker

All Answers

Parker EdelmannParker Edelmann
Hello @econ242,

I don't know the days of your fiscal quarters, so I'll use December 31st, March 31st, June 30th, and September 30th instead in the below formula. 
DATE(
     YEAR(TODAY), 
     CASE(MONTH(TODAY),1,3,
                       2,3,
                       3,3,
                       4,6,
                       5,6,
                       6,6,
                       7,9,
                       8,9,
                       9,9,
                       10,12,
                       11,12,
                       12,12, null),
     CASE(MONTH(TODAY),1,31, 2,31, 3,31, 4,30, 5,30, 6,30, 7,30, 8,30, 9,30, 10,31, 11,31, 12,31, null)
     )

This will give you the last day of the current calendar quarter.

I hope this helps.

Thanks,
Parker

This was selected as the best answer
econ242econ242
Hi Parker,

Thanks so much...only thing that was missing was () after TODAY...so I fixed that and no errors. But of course, lol...as I tested this morning, and it being the last day of this quarter, I can't really tell if it's adjusting properly, lol... I did set the close date out to a random day July and it did readjust to June 30, 2016 so I'm thinking it works. Just to be sure,  I will test tomorrow to see if it pushes out to September 30, 2016. But I'm confident this did it!!!

Thanks again!!!

Eric

 
Parker EdelmannParker Edelmann
Sorry about that, rookie mistake to forget the () :-(. Anyways, I'm glad it appears to be working. Let me know tommorow if it works. Like you, I'm confident it will!

Thanks,
Parker