calculate the exact number of working days in the last fullmonth 1

[ginkoba]

I'm using the below formula to derive the exact number of working days(-sat, sun) for the last full month. For some reason, each month, I have to manually adjust for 1 or a couple of days depending if the month has 30 or 31 days. I thought my formula was doing this automatically but some how, its not. Can someone please take a look and let me know.

NumberVar v_monthvalue;
if month(currentdate) in [4,6,9,11]
then v_monthvalue := 32
else if month(currentdate) = 2
then v_monthvalue := 28
else v_monthvalue := 29;
Local DateTimeVar d1 := CurrentDate-v_monthvalue;
Local DateTimeVar d2 := CurrentDate;
DateDiff ("d", d1, d2) -
DateDiff ("ww", d1, d2, crSaturday) -
DateDiff ("ww", d1, d2, crSunday)

 

[IanWaterman]

This question has been asked many times.
You will be better of using this tried and tested method

http://kenhamady.com/form01.shtml

Ian

 

[ginkoba]

Thanks Ian. I'm aware of that formula but in my case, there are no prompts/parameters dates to count in between. I'm using the lastfullmonth. The thing is, I want this report to run every 5th or whatever of the month for the lastfullmonth without me putting in the parameters. So if today is August 16th, I want it to run for July 1 thru Jul 31st without me having to put in the starting and ending dates.

 

[MCuthill]

ginkoba,

Having not looked at the formula to which Ian speaks, could you substitute the Parameters with a pair of formula fields for Minimum & Maximum Dates?

{@Last Month}
DateAdd("m",-1,CurrentDate)

{@StartLastMonth}
Date(Year({@LastMonth}),Month({@LastMonth}),1)

{@EndLastMonth}
Date(Year(CurrentDate),Month(CurrentDate),1)-1

Hope this helps!

Mike
---------------------------------------------------------------
EvE Online: Claim the Destiny that is Rightfully Yours...

 

[ginkoba]

Thanks much Mike. I was able to modify Ken's formula along with a modified version of your formula producing the following: I did not include the holidays since I don't require them here just yet. You will note that I replaced "-1" with "-2" in the last line before "weeks + days" in order to get 5 working days.

WhileReadingRecords;
Local DateVar Start := {@StartLastMonth};
Local DateVar End := {@EndLastMonth};
Local NumberVar Weeks;
Local NumberVar Days;

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 -2 else 0);

Weeks + Days