## 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.

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

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

## RE: Calculating Days in a month based on Current Date

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

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

Skip,

_{ Just traded in my OLD subtlety... for a NUance!}## RE: Calculating Days in a month based on Current Date

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

## RE: Calculating Days in a month based on Current Date

Thanks,

Brad

## RE: Calculating Days in a month based on Current Date

## RE: Calculating Days in a month based on Current Date

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

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

## RE: Calculating Days in a month based on Current Date

## CODE

## RE: Calculating Days in a month based on Current Date

Shouldn't it be in braces{}

Skip,

_{ Just traded in my OLD subtlety... for a NUance!}## RE: Calculating Days in a month based on Current Date

{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,

_{ Just traded in my OLD subtlety... for a NUance!}## RE: Calculating Days in a month based on Current Date

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

Skip,

_{ Just traded in my OLD subtlety... for a NUance!}## RE: Calculating Days in a month based on Current Date

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

Skip,

_{ Just traded in my OLD subtlety... for a NUance!}## RE: Calculating Days in a month based on Current Date

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

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

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

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

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

## CODE

Here are the formulas that rely on DaysFromStart.

RoundDays

## CODE

WorkDays

## CODE

GoalPerDay

## CODE

GoalDay

## CODE

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

## RE: Calculating Days in a month based on Current Date

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

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

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

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