×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

*
*
*
*

Thanks. We have received your request and will respond promptly.

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Calculating Days in a month based on Current Date

thread767-1770778

Calculating Days in a month based on Current Date

Calculating Days in a month based on Current Date

(OP)
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.

CODE

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

RE: Calculating Days in a month based on Current Date

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.

RE: Calculating Days in a month based on Current Date

(OP)
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.

RE: Calculating Days in a month based on Current Date

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

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Calculating Days in a month based on Current Date

Okay if you need end of month:

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

RE: Calculating Days in a month based on Current Date

(OP)
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

RE: Calculating Days in a month based on Current Date

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

RE: Calculating Days in a month based on Current Date

(OP)
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.

RE: Calculating Days in a month based on Current Date

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}

RE: Calculating Days in a month based on Current Date

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

CODE

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

RE: Calculating Days in a month based on Current Date

The Date() function IS DateTime already!

Shouldn't it be in braces{}

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Calculating Days in a month based on Current Date

BTW, if your Document_Date contains a Time component, then your filter ought to be...

{DateField}>= date(year(currentdate),month(currentdate),1) and {DateField} < date(year(currentdate),month(currentdate)+1,1)

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,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Calculating Days in a month based on Current Date

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/

RE: Calculating Days in a month based on Current Date

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

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Calculating Days in a month based on Current Date

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/

RE: Calculating Days in a month based on Current Date

Ken, thanks for the feedback.

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Calculating Days in a month based on Current Date

(OP)
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

RE: Calculating Days in a month based on Current Date

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/

RE: Calculating Days in a month based on Current Date

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.

RE: Calculating Days in a month based on Current Date

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/

RE: Calculating Days in a month based on Current Date

(OP)
In Crystal 2008 I'm having an issue with the formula listed above which calculates the goal Sales dollars for each work day from the start of the month to the current day. below is the current formula called DaysFromStart.

CODE

WhileReadingRecords;
Local DatetimeVar Start := dateserial(year(currentdate),month(currentdate),1);
Local DatetimeVar End := {Invoice_Header.Document_Date};
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) + 1 + 
(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
It calculates the first day correctly but each day after the first is incorrect.

Here are the formulas that rely on DaysFromStart.

RoundDays

CODE

Round ({@DaysFromStart})

WorkDays

CODE

WhileReadingRecords;
Local DatetimeVar Start := dateserial(year(currentdate),month(currentdate),1);
Local DatetimeVar End := dateserial(year(currentdate),month(currentdate)+1,1)-1;
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) + 1 + 
(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

GoalPerDay

CODE

1000000/{@WorkDays}

GoalDay

CODE

{@RoundDays}*{@GoalPerDay}

Thank you for your help it's very appreciated!!!

RE: Calculating Days in a month based on Current Date

So one formula calculates the number of working days in the current calendar month, and another calculates the number of working days between the first of the calendar month and the transaction date. My first step would be to place these two formulas on the details band and confirm that they are both calculating the correct number of business days based on the transaction date of that record.

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/

RE: Calculating Days in a month based on Current Date

(OP)
Ken,
I did what you suggested and the @workDays formula is correct. Everything is calculating correctly but the chart is showing incorrect values. Here is an example of the data.

CODE

Work Days this month= 22
DaysFromStart       GoalDay
1                  $45,454.55
2                  $90,909.09
3                  $136,363.64

The strange thing is how the Chart displays these numbers.
DaysFromStart     GoalDay
1                  $51,000.00 (approximate)
2                  $149,000.00 (approximate)
3                  $249,000.00 (approximate)

It appears there is something wrong with the Chart so I'm going to look there.

Thanks for your help.
Brad

RE: Calculating Days in a month based on Current Date

(OP)
Mystery solved!
I'm using a stacked graph and it's adding the DayInv total to the GoalDay total. Thats why the GoalDay total is so high and erratic.

Thanks,
Brad

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login
Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close