Community
Showing results for 
Search instead for 
Do you mean 
Reply

Date calculation in custom field on report

Accepted Solution Solved
Copper Contributor
Posts: 48
Country: USA
Accepted Solution

Date calculation in custom field on report

ACT! has custom calculated date fields for the database (talking v16 here).  But they calculate against "today".  I need to do a similar calculation to the "Days Open" calculation on the Opportunities tab.  That looks at open date and actual close to provide an integer representing the time between the opportunity being open and when it was finally closed.

 

In our case, the process runs like this:

 

1. RFQ comes in

2. Quote sent out 

3. Decision (close) is made one way or the other

 

The "Days Open" gives us a measurement between 1 and 3, but we need one between 1 and 2.

 

I put a custom field in the Opportunity report I built.  First I used DATEDIFF.  Then I tried just subtracting one field from the other.  I put them both in as expressions and then I wrote scripts.  None of these worked.

This is one variation of the script I tried:

 

If IsNull(FEstimatedCloseDate2) Then

Custom4.Text = ""
Else
Custom4.Text = CStr(DateDiff("d", DateValue(OpenDate1.text), DateValue(FEstimatedCloseDate2.text)))
EndIf

 

I'm guessing that there's some small variant of VBA that I've got wrong and I'm just not seeing it.  Or I'm not allowed to do this.  Either way, I'd appreciate a sharper pair of eyes than mine.

Thanks.

 

jma

 


Accepted Solutions
Solution
Accepted by topic author jaldrichbardons
‎09-25-2015 03:20 AM
Copper Contributor
Posts: 48
Country: USA

Re: Date calculation in custom field on report

FWIW ... I figured the out the problem:

 

This script works - 

 

If IsNull(FEstimatedCloseDate2) Then
Custom4.Text = ""
Else
Custom4.Text = CHR(34) & CStr(DateDiff("d", OpenDate1.value, FEstimatedCloseDate2.value)) & CHR(34)
EndIf

 

I needed to put double quotes - CHR(34) - around the text, and since the fields were dates already, I referred to the value of the fields, rather than trying to make the formula look at it as text and then convert it to the date value in order to make DATEDIFF (the number of days between two dates) work.

View solution in original post


All Replies
Solution
Accepted by topic author jaldrichbardons
‎09-25-2015 03:20 AM
Copper Contributor
Posts: 48
Country: USA

Re: Date calculation in custom field on report

FWIW ... I figured the out the problem:

 

This script works - 

 

If IsNull(FEstimatedCloseDate2) Then
Custom4.Text = ""
Else
Custom4.Text = CHR(34) & CStr(DateDiff("d", OpenDate1.value, FEstimatedCloseDate2.value)) & CHR(34)
EndIf

 

I needed to put double quotes - CHR(34) - around the text, and since the fields were dates already, I referred to the value of the fields, rather than trying to make the formula look at it as text and then convert it to the date value in order to make DATEDIFF (the number of days between two dates) work.