Calculating Days in a month based on Current Date 1

[BradCustom]

Hi, thanks for your help with this problem!

I'm using Crystal 2011 and I have a report which shows the actual sales vs. sales goals for a given time period. The time period is defined by the user entering a start and end date into a parameter called InvoiceDate . Typically the start and end of a month. What I would like to do is automatically select the start and end date based on the current date. For example; if I ran the report today 10/17/2016 then my start date would be 10/1/2016 and the end date would be 10/31/2016. below is the formula I'm currently using to calculate the work days between the start and end of range.

WhileReadingRecords;
Local DatetimeVar Start := {@MinDate};   // place your Starting Date here
Local DatetimeVar End := {Invoice_Header.Document_Date};  // place your Ending Date here
Local NumberVar Weeks; 
Local NumberVar Days; 
Local Numbervar Hol;
DateVar Array Holidays;

Weeks:= (Truncate (End - dayofWeek(End) + 1 
- (Start - dayofWeek(Start) + 1)) /7 ) * 5;
Days := DayOfWeek(End) - DayOfWeek(Start) + 2 + 
(if DayOfWeek(Start) = 1 then -1 else 0)  + 
(if DayOfWeek(End) = 7 then -1 else 0);   

Local NumberVar i;
For i := 1 to Count (Holidays)
do (if DayOfWeek ( Holidays[i] ) in 2 to 6 and 
     Holidays[i] in start to end then Hol:=Hol+1 );

Weeks + Days

 

[Charliy]

Your start date would be: date(year(currentdate),month(currentdate),1)

Unless your data can tell the future, the currentdate should be your end date.

 

[BradCustom]

Thanks Charliy,
I've been trying to get your suggestion to work but unfortunately, I can't get the result for the current month. Instead I get everything from the beginning to the current date.

 

[SkipVought]

StartOfMonth = date(year(currentdate),month(currentdate),1)
EndOfMonth = date(year(currentdate),month(currentdate)+1,1)-1

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[Charliy]

Okay if you need end of month:

date(year(currentdate),month(currentdate)+1,1)-1

 

[BradCustom]

I still can't get this to work and I believe its because I've asked the wrong question. What I'd like to do is limit the record selection to Invoices posted in the current month. I do that now with a parameter field which requires you to enter a start and end date.
Thanks,
Brad

 

[kray4660]

Use SkipVought formulas as your filter. i.e., {DateField}>= date(year(currentdate),month(currentdate),1) and {DateField} <= date(year(currentdate),month(currentdate)+1,1)-1

 

[BradCustom]

I tried that in the record selection and the result was no records.

I just noticed that your formula is slightly different than mine so I will try it.

 

[Charliy]

What does you select formula look like? If you set up the begin and end formulas that you select should contain:

{table.date} in {@BeginDate} to {@EndDate}

 

[BradCustom]

this is what I tried which I can now see why it doesn't work.

{Invoice_Header.Document_Date} in DateTime (date(year(currentdate),month(currentdate),1)) to DateTime (date(year(currentdate),month(currentdate)+1,1)-1)

 

[SkipVought]

The Date() function IS DateTime already!

Shouldn't it be in braces{}

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[SkipVought]

BTW, if your Document_Date contains a Time component, then your filter ought to be...
[tt]
{DateField}>= date(year(currentdate),month(currentdate),1) and {DateField} < date(year(currentdate),month(currentdate)+1,1)
[/tt]
Notice it states
Greater Than or Equal {First of The Month} And
Less Than {First of Next Month}

This catches time values in the last day of the month.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[kenhamady]

I realize that this is an old thread, but I figured I would jump in since the formula at the top is mine. One thing you CAN'T do is this:
date(year(currentdate),month(currentdate)+1, 1)

This will seem to work until you get to December and then it will error. That is because it adds the number 1 to the month NUMBER. If you add 1 to 12 you get 13, which will error on you. Better formulas for the first and last of the month would be:

//{@Begin of Month}
CurrentDate - Day (CurrentDate) +1

//{@End of Month}
DateAdd('m',1, {Begin of Month})-1

Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guides to Formulas, Parameters, Subreports, Cross-tabs, VB, Tips and Tricks

http://www.kenhamady.com/

 

[SkipVought]

Works in Excel. Figgured it would also work in CR.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[kenhamady]

Skip,

Yeah, they work differently. CR requires that the month number in a date or datetime function always be between 1 and 12 and the day be between 1 and the end of that month. Excel apparently doesn't care and just rolls forward. In a spreadsheet this formula: =Date(2016,15,33) returns 4/2/2017. Starts in 2016 and returns the 15th month and the 33rd day.

Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guides to Formulas, Parameters, Subreports, Cross-tabs, VB, Tips and Tricks

http://www.kenhamady.com/

 

[SkipVought]

Ken, thanks for the feedback.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[BradCustom]

Ken,
If I read your post correctly you'd replace the code below.
StartOfMonth = date(year(currentdate),month(currentdate),1)
EndOfMonth = date(year(currentdate),month(currentdate)+1,1)-1

with is code?
//{@Begin of Month}
CurrentDate - Day (CurrentDate) +1

//{@End of Month}
DateAdd('m',1, {Begin of Month})-1


Thanks,
Brad

 

[kenhamady]

You could do it that way if you make sure you use := instead of just =, and refer to the variable in the second one instead of a field.

However I would probably write the two formulas separately and just refer to them in here. This makes it easier to troubleshoot and makes them available for other calculations or for display purposes.

Also, DateAdd returns a DateTime value while the first formula returns a Date value, so you might need to change your variable declarations one way or the other.

Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guides to Formulas, Parameters, Subreports, Cross-tabs, VB, Tips and Tricks

http://www.kenhamady.com/

 

[AndyMc]

If you want that Excel ability to understand dates, use DateSerial() function instead of Date().
That function knows that month 12 plus 1 equals January and automatically know the year is one month later too.

 

[kenhamady]

Andy,

Wow, always great to learn something new. That might merit a mention on my blog.

Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guides to Formulas, Parameters, Subreports, Cross-tabs, VB, Tips and Tricks

http://www.kenhamady.com/