Date Formula - consolidation of days. 1

[rnd]

I have a need to consolidate my cash for each calendar month. My payout bills are spreaded over two calendar months. Ex: January 10th to February 09th. This trend continues for the entire year.
My need: For January I need to take the number of days and cash amount from 10th to the end of the month from this bill and take the number of days and cash amount from the beginning of the month to 9th from the previous bill and add them all together - so that I can have my cash amount adjusted for the number of days in each calender month appropriately.
Please help me in calculating the date part (syntax) to match with the calendar month-days. Cash part I can take care.
Thanks
RND

 

[kenhamady]

What version of CR?

Do you mean the number of days that exist on the calendar between two dates?

Or do you mean the number of days that have records in the database? Ken Hamady

http://www.kenhamady.com/

On-site custom Crystal Reports Training and Consulting.
Quick Reference Guide to using Crystal in VB.

 

[rnd]

CR Version - 8.0.1.0
DataBase - MS SQL Server 7.

It is the calendar days. Weekends or holidays does not matter. I want to calculate the number of days 1. From the beginning of the month to the date of one bill. 2. From the date of the other bill to the end of the month. 3. Then I can add them to gether to get the correct total number of days in that month and find the amount of cash for that month.
EX: Assume Bill Dates 5/10/2001 to 6/09/2001,
6/10/2001 to 7/12/2001.
To figure out the amount for June (6) I need the syntax for a formula looking like this:
FromTheBeginningOfTheMonth to 6/09/2001(Days{.BillDate}) +
6/10/2001 (Days{.BillDate}) to EndOfTheMonth.
I believe as such I could not see any date or date rangeformula counting number of days left in a month.
I am looking some syntax for
FromTheBeginningOfTheMonth
ToTheEndOfTheMonth.
Thanks
Das

 

[kenhamady]

If you have any date field and you subtract the "days" portion from the date, you will get the last day of the previous month. Adding one will give you the first day of this month:


{BillDate} - Day({BillDate}) + 1

this will work with any date to give you the first day of the month for that date.

If you create a formula that adds one month to the date first (use the DateAdd() function). Then you can use that formula to get the last date of the month:

{@newdate} - Day({@newdate})
Ken Hamady

http://www.kenhamady.com/

On-site custom Crystal Reports Training and Consulting.
Quick Reference Guide to using Crystal in VB.