Community
Showing results for 
Search instead for 
Do you mean 
Reply

ACT! Premium Calculated fields

Accepted Solution Solved
New Member
Posts: 10
Country: Canada
Accepted Solution

ACT! Premium Calculated fields

Hello All,

 

I am new to ACT! (v17) and I have been tasked with editing the opportunity tab. The users would like to add two date field (start and close) and have a calculated field that adds up the days between open and closed. I have a tried a couple of things but nothing is working. I have searched some but have not found any examples. What would be easiest way to do this? 

 

Thanks - Ryan 


Accepted Solutions
Solution
Accepted by topic author ryan697
‎06-10-2016 09:03 AM
Moderator
Posts: 131
Country: United_Kingdom

Re: ACT! Premium Calculated fields

Hi there, the Days Open is actually calculated based on the Open Date, not on the Create Date as per the below screenshot:

 

 

Screenshot 1.png

 

Now the Days Open will continue to add up as long as the Opportunity is Open. So even if you put in an Actual Close Date, the Days Open will still continue to add up.

 

Once the Opportunity is Closed AND an Actual Close Date is entered, then the Days Open will calculate correctly.

 

I hope this makes sense Ryan, please let me know if you have any further queries.

View solution in original post


All Replies
Platinum Elite Contributor
Posts: 6,652
Country: USA

Re: ACT! Premium Calculated fields

All thse fields already exist. Create or Open date, Actual Close Date and Days open

Roy Laudenslager
ACT! Certified Consultant
ACT! Report Expert
Durkin Impact Report Designer
www.techbenders.com
royel@techbenders.com
541-343-8129
New Member
Posts: 10
Country: Canada

Re: ACT! Premium Calculated fields

I know these fields already exist. What I need to create is new field that will calculate how many days between open and close, but I am not sure how to accomplish. I have search for some examples but nothing. 

Platinum Elite Contributor
Posts: 6,652
Country: USA

Re: ACT! Premium Calculated fields

The days open field gives you that information. It updates the calculation on a daily basis until the opportunity is closed at which point it shows the number of days from when the opportunity was created until it's closed (Actual Close Date).

Roy Laudenslager
ACT! Certified Consultant
ACT! Report Expert
Durkin Impact Report Designer
www.techbenders.com
royel@techbenders.com
541-343-8129
Moderator
Posts: 131
Country: United_Kingdom

Re: ACT! Premium Calculated fields

Hi Ryan, Roy is quite right, the best field to use would be the "Days Open" field. This is automatically calculated based on the "Open Date" and the "Actual Close Date" fields, so it does require you to use those fields.

 

That calculation cannot be performed using a calculated field. The problem is, you could create a calculation that told you how long it was since the open date, but it would continue to count permanently even after the Opportunity was closed. Because of this, I'd strongly recommend sticking with the "Days Open" field.

 

I hope this helps Ryan.

New Member
Posts: 10
Country: Canada

Re: ACT! Premium Calculated fields

Thanks for the replies so far, it has helped. It appears the days open calculates from the date created. I would like it to be from open and closed. I have attached a screen shot of how it is calculating right now.    

Platinum Elite Contributor
Posts: 6,652
Country: USA

Re: ACT! Premium Calculated fields

As I noted before the Day's Open calculates from the create date. Because all date and time fields in SQL have both a date and time component, a simple field calculator won't work even if you added new fields. The best way to get what you want would b to use a report where a VB script can be used to do the date calculation.

Roy Laudenslager
ACT! Certified Consultant
ACT! Report Expert
Durkin Impact Report Designer
www.techbenders.com
royel@techbenders.com
541-343-8129
Solution
Accepted by topic author ryan697
‎06-10-2016 09:03 AM
Moderator
Posts: 131
Country: United_Kingdom

Re: ACT! Premium Calculated fields

Hi there, the Days Open is actually calculated based on the Open Date, not on the Create Date as per the below screenshot:

 

 

Screenshot 1.png

 

Now the Days Open will continue to add up as long as the Opportunity is Open. So even if you put in an Actual Close Date, the Days Open will still continue to add up.

 

Once the Opportunity is Closed AND an Actual Close Date is entered, then the Days Open will calculate correctly.

 

I hope this makes sense Ryan, please let me know if you have any further queries.

New Member
Posts: 10
Country: Canada

Re: ACT! Premium Calculated fields

Yes, after changing the status to closed it does what I want. The user wanted the default status changed to different options. I initially removed that and added a custom drop. Thanks for the help!  

Nickel Elite Contributor
Posts: 934
Country: USA

Re: ACT! Premium Calculated fields

ACT!v17 - I recommend you watch out for the losing of date/time data from date/time fields.

Reference my post at http://community.act.com/t5/Act/Act-Pro-Ver-17-2-217-0-Hot-Fix-3-looses-data-in-date-time-fields/td-...

 

Bill Williams - Owner/Consultant/Trainer at Fill Staff "ACT! FOR RECRUITERS"
Manage Your ACT! Backups http://fillstaff.com/backupfolderspro.html - FREE
BIG ACT! LAYOUTS for Larger Monitors www.bigactlayouts.com
Protect your Files & Folders with Folder Lock Pro by New Software http://download.cnet.com/Folder-Lock/3000-2092_4-10063343.html