Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations bkrike on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
Business date count for each month 3
- Thread starter silck
- Start date
-
1
- #2
Dear Slick,
If you only need to count weekdays then this formula will work. It subtracts all saturdays and sundays. If you need to exclude holidays, see Ken Hamady's FAQ.
DateDiff ("d", datefield1, datefield2) -
DateDiff ("ww", datefield1, datefield2, crSaturday) -
DateDiff ("ww", datefield1, datefield2, crSunday)
You must supply it datefield1 and datefield2. If you need help for formula for 1st day of Month and Last day of Month, let me know.
ro
Rosemary Lieberman
rosemary@microflo.com, Microflo provides expert consulting on MagicTSD and Crystal Reports.
If you only need to count weekdays then this formula will work. It subtracts all saturdays and sundays. If you need to exclude holidays, see Ken Hamady's FAQ.
DateDiff ("d", datefield1, datefield2) -
DateDiff ("ww", datefield1, datefield2, crSaturday) -
DateDiff ("ww", datefield1, datefield2, crSunday)
You must supply it datefield1 and datefield2. If you need help for formula for 1st day of Month and Last day of Month, let me know.
ro
Rosemary Lieberman
rosemary@microflo.com, Microflo provides expert consulting on MagicTSD and Crystal Reports.
-
2
- #3
The following will give you a weekday count (CR 8 and higher).
datevar firstday;
datevar lastday;
numbervar loop;
local numbervar wds;
numbervar span;
firstday:=date(year({@date}),month({@date}),1);
if month(firstday)=12 then lastday:=date(year(firstday)+1,1,1)-1
else lastday:=date(year(firstday),month(firstday)+1,1)-1;
span:=lastday-firstday;
For loop:= 0 to span do(
if dayofweek(firstday+loop)in [2 to 6] then wds:=wds+1 else wds:=wds);
if date(2001,12,25) in firstday to lastday then wds:=wds-1;
if date(2001,11,22) in firstday to lastday then wds:=wds-1;
wds
Add the appropriate holidays (the lines in blue). You can then reference the formula in other formulas.
Mike
datevar firstday;
datevar lastday;
numbervar loop;
local numbervar wds;
numbervar span;
firstday:=date(year({@date}),month({@date}),1);
if month(firstday)=12 then lastday:=date(year(firstday)+1,1,1)-1
else lastday:=date(year(firstday),month(firstday)+1,1)-1;
span:=lastday-firstday;
For loop:= 0 to span do(
if dayofweek(firstday+loop)in [2 to 6] then wds:=wds+1 else wds:=wds);
if date(2001,12,25) in firstday to lastday then wds:=wds-1;
if date(2001,11,22) in firstday to lastday then wds:=wds-1;
wds
Add the appropriate holidays (the lines in blue). You can then reference the formula in other formulas.
Mike
Dear Mike,
Very nice!
ro Rosemary Lieberman
rosemary@microflo.com, Microflo provides expert consulting on MagicTSD and Crystal Reports.
Very nice!
ro Rosemary Lieberman
rosemary@microflo.com, Microflo provides expert consulting on MagicTSD and Crystal Reports.
Rosemary,
I compared the two formulas, using my method of finding first and last dates for the month, and am getting conflicting results. July 2002 is showing me 22 weekdays using your formula, while mine results in 23. It should be 23
I double checked using specific dates:
datevar fd:=date(2002,1,1);datevar ld:=date(2002,1,31);
numbervar week ;
week:=DateDiff ("d", fd, ld) -
DateDiff ("ww", fd, ld, crSaturday) -
DateDiff ("ww", fd, ld, crSunday) Mike
I compared the two formulas, using my method of finding first and last dates for the month, and am getting conflicting results. July 2002 is showing me 22 weekdays using your formula, while mine results in 23. It should be 23
I double checked using specific dates:
datevar fd:=date(2002,1,1);datevar ld:=date(2002,1,31);
numbervar week ;
week:=DateDiff ("d", fd, ld) -
DateDiff ("ww", fd, ld, crSaturday) -
DateDiff ("ww", fd, ld, crSunday) Mike
- Thread starter
- #6
- Thread starter
- #8
Dear MBarron,
You are right! I checked it out, and thought it was weird.
This is a standard Crystal Reports example formula!
I thought, hmm simply adding 1 should fix, since it is a datediff it must be excluding the first day. Wrong!
It fixes it for all months except for those that start or end on a Sunday, because those were right to begin with!
Also, it is right on some months. For example, your formula and my formula agree for June - 20 days for both!
In investigating this I found when you look at the help for the datediff("ww", ... it states:
//begin quote:
Use DateDiff with the "ww" parameter to calculate the number of firstDayOfWeek's occurring between two dates. For the DateDiff function, the "ww" parameter is the only one that makes use of the firstDayOfWeek argument. It is ignored for all the other interval type parameters. For example, if firstDayOfWeek is crWednesday, it counts the number of Wednesday's between startDateTime and endDateTime. It does not count startDateTime even if startDateTime falls on a Wednesday, but it does count endDateTime if endDateTime falls on a Wednesday..
//end quote
So the issue seems that for my formula to be correct I need to test to see if the First Date of the Month is a Sunday or Saturday.
Here is a corrected formula, using your bits for firstday and lastday:
//begin formula
datevar fd;
datevar ld;
fd:=date(year({currentdate}),month(currentdate),1);
if month(fd)=12 then ld:=date(year(fd)+1,1,1)-1
else ld:=date(year(fd),month(fd)+1,1)-1;
numbervar week ;
week:=(DateDiff ("d", fd, ld) -
DateDiff ("ww", fd, ld, crSaturday) -
DateDiff ("ww", fd, ld, crSunday)) +1
;
if DayOfWeek (fd) = crsunday then Week:= week - 1 ;
if DayOfWeek (fd) = crSaturday then Week:= Week - 1;
week
//end formula
Thanks so much for pointing this out - I just took the example at face value and should have investigated it more.
Glad I gave you that star now
... Rosemary Lieberman
rosemary@microflo.com, Microflo provides expert consulting on MagicTSD and Crystal Reports.
You are right! I checked it out, and thought it was weird.
This is a standard Crystal Reports example formula!
I thought, hmm simply adding 1 should fix, since it is a datediff it must be excluding the first day. Wrong!
It fixes it for all months except for those that start or end on a Sunday, because those were right to begin with!
Also, it is right on some months. For example, your formula and my formula agree for June - 20 days for both!
In investigating this I found when you look at the help for the datediff("ww", ... it states:
//begin quote:
Use DateDiff with the "ww" parameter to calculate the number of firstDayOfWeek's occurring between two dates. For the DateDiff function, the "ww" parameter is the only one that makes use of the firstDayOfWeek argument. It is ignored for all the other interval type parameters. For example, if firstDayOfWeek is crWednesday, it counts the number of Wednesday's between startDateTime and endDateTime. It does not count startDateTime even if startDateTime falls on a Wednesday, but it does count endDateTime if endDateTime falls on a Wednesday..
//end quote
So the issue seems that for my formula to be correct I need to test to see if the First Date of the Month is a Sunday or Saturday.
Here is a corrected formula, using your bits for firstday and lastday:
//begin formula
datevar fd;
datevar ld;
fd:=date(year({currentdate}),month(currentdate),1);
if month(fd)=12 then ld:=date(year(fd)+1,1,1)-1
else ld:=date(year(fd),month(fd)+1,1)-1;
numbervar week ;
week:=(DateDiff ("d", fd, ld) -
DateDiff ("ww", fd, ld, crSaturday) -
DateDiff ("ww", fd, ld, crSunday)) +1
;
if DayOfWeek (fd) = crsunday then Week:= week - 1 ;
if DayOfWeek (fd) = crSaturday then Week:= Week - 1;
week
//end formula
Thanks so much for pointing this out - I just took the example at face value and should have investigated it more.
Glad I gave you that star now
... Rosemary Liebermanrosemary@microflo.com, Microflo provides expert consulting on MagicTSD and Crystal Reports.
Correction:
It fixes it for all months except for those that start or end on a Sunday, because those were right to begin with!
The line above in my previous post should state:
It fixes it for all months except for those that start on a Saturday or Sunday, because those were right to begin with!
just so that it states it correctly...
ro Rosemary Lieberman
rosemary@microflo.com, Microflo provides expert consulting on MagicTSD and Crystal Reports.
It fixes it for all months except for those that start or end on a Sunday, because those were right to begin with!
The line above in my previous post should state:
It fixes it for all months except for those that start on a Saturday or Sunday, because those were right to begin with!
just so that it states it correctly...
ro Rosemary Lieberman
rosemary@microflo.com, Microflo provides expert consulting on MagicTSD and Crystal Reports.
Rose,
First off, thanks for the Star and the compliment. It feels good getting them from the people I consider gurus.
I like the formula supplied. I'm stealing it for my reports.![[smile]](/data/assets/smilies/smile.gif "[smile] [smile]")
I've made one small change to the fomula you supplied:
numbervar week ;
week:=DateDiff ("d", fd, ld) -
DateDiff ("ww", fd, ld, crSaturday) -
DateDiff ("ww", fd, ld, crSunday);
if dayofweek(fd) in [2,3,4,5,6] then week:=week+1;
week
Mike
First off, thanks for the Star and the compliment. It feels good getting them from the people I consider gurus.
I like the formula supplied. I'm stealing it for my reports.
I've made one small change to the fomula you supplied:
numbervar week ;
week:=DateDiff ("d", fd, ld) -
DateDiff ("ww", fd, ld, crSaturday) -
DateDiff ("ww", fd, ld, crSunday);
if dayofweek(fd) in [2,3,4,5,6] then week:=week+1;
week
Mike
Dear Mike,
Once again, nice job! That change definitely makes it more elegant!
One of the reasons that I love the forums here is that through collaboration we are all able to improve our formulas and skills!
I am truly happiest when I learn something new.
Ro Rosemary Lieberman
rosemary@microflo.com, Microflo provides expert consulting on MagicTSD and Crystal Reports.
Once again, nice job! That change definitely makes it more elegant!
One of the reasons that I love the forums here is that through collaboration we are all able to improve our formulas and skills!
I am truly happiest when I learn something new.
Ro Rosemary Lieberman
rosemary@microflo.com, Microflo provides expert consulting on MagicTSD and Crystal Reports.
- Status
Similar threads
- Locked
- Question
- Locked
- Question
- Locked
- Question