Method:Classic General Q&A

Re: Date Difference as Integer in Report Designer

Anonymous — Mon, 02 Jul 2012 19:29:44 GMT

CDS_Josh:
I had everything working fine and with ZERO changes to the above formulas

Did anything change with the data in your Method account? A formula should not just stop working unless the data changed. Are you testing this in the preview window or with the screen generating the report. It's preferred to test the formula with the actual results instead of the preview window. If that's the case, and you're still having a problem with this forumla, then maybe take the complexity down to just 30, 60 or 90. Run those formulas independantly and see if the data is still correct. I assume the 30Days, 60Days and 90Days are all calculated fields?

Re: Date Difference as Integer in Report Designer

CDS_Josh — Thu, 28 Jun 2012 18:11:01 GMT

I had everything working fine and with ZERO changes to the above formulas, now it is no loner working correctly. It just groups everything in "<= 30 Days". I did not change a thing and it just stops working.

Can we open this thread back up for suggestions?

Re: Date Difference as Integer in Report Designer

CDS_Josh — Thu, 28 Jun 2012 17:32:04 GMT

I figured out a difference approach. I created 3 Date Fields using the AddDay function for 30,60,90 days ago and then made my formula based off that.

I really wanted to see the DATEDIFF fuction get incorporated because this is a great tool for aging reports.

Thanks anyways. I think I figure out what I need!!

Here is my code for future reference and for others:

30Days: AddDays(Today(), -30)

60 Days: AddDays(Today(), -60)

90 Days: AddDays(Today(), -90)

AgedGroup: Iif([CallReport.DateRcvd] >= [CallReport.Day30Date], '≤30 Days',Iif([CallReport.DateRcvd] < [CallReport.Day30Date] AND [CallReport.DateRcvd] >= [CallReport.Day60Date],'31-60 Days' ,Iif([CallReport.DateRcvd] < [CallReport.Day60Date] AND [CallReport.DateRcvd] >= [CallReport.Day90Date],'61-90 Days' ,'>90 Days')))

Re: Date Difference as Integer in Report Designer

CDS_Josh — Thu, 28 Jun 2012 16:52:35 GMT

Thanks for the reply, but I have already tried what you suggested and still get a date showing up in excel. I know I can change the format in excel, but I have another calculated field in the report that runs off the "Days Aged" field.

Here are the calculations for my fields:

AgedDays: Now() - [CallReport.DateReceived]

AgedGroup: Iif([AgeDays] <= 30, '≤30 Days', Iif([AgeDays] > 30 And [AgeDays] <= 60, '31-60 Days',Iif([AgeDays] > 60 And [AgeDays] <= 90, '31-60 Days', '>90 Days')))

I have the AgedDays set to integer

and here are my results.

This record should has fallen into the >90 Days group, since it is 107 Days ago (if you convert 4/15/1900 to number in excel). Am I missing something?

Re: Date Difference as Integer in Report Designer

Anonymous — Thu, 28 Jun 2012 16:20:10 GMT

CDS_Josh:
Is this possible? I can get it to show as a date, but cannot convert to an integer or number

I'm not sure exactly what you're trying to do but I'll try to point you in the right direction. If you look at the Edit screen for your calculated fields you should be able to change the field type to an Integer. Then if you click into the expression property above, you should be able to build an expression to combine the fields.

HTH

Date Difference as Integer in Report Designer

CDS_Josh — Wed, 27 Jun 2012 19:38:38 GMT

I am having a heck of a time getting a Date Difference field to show as an Integer in report designer. I also want to use this calculated field within another field to show Aging (30, 60, 90, >90).

Is this possible? I can get it to show as a date, but cannot convert to an integer or number

Thanks in Advance!!

-Josh