Here’s a good one. I need to calculate the number of days between two dates. Normally not a problem… but I don’t want to count weekends in the calculation. So if I wanted to get the number of days between today (Wednesday) and last Tuesday, I need to result to be 6 (8 total days - the two weekend days). Has anyone ever had to do this?
Listserv Archives (BOB member since 2002-06-25)
Ted,
The SQL below is written for DB2 and calculates the weekdays between two dates. DB2 doesn’t have the range of scalar functions of some of the other RDBMS’s, so you may be able to accomplish this easier if you are on a different platform.
SUM(INTEGER((DAYS(End_Date)-DAYS(Begin_Date))/7)*5 +1 +CASE WHEN DAYS(End_Date)-
INTEGER((DAYS(End_Date))/7)*7 < DAYS(Begin_Date)- INTEGER((DAYS(Begin_Date))/7)*7 THEN (DAYS(End_Date)- INTEGER((DAYS(End_Date))/7)*7) - (DAYS(Begin_Date)- INTEGER((DAYS(Begin_Date))/7)*7) +5 ELSE (DAYS(End_Date)- INTEGER((DAYS(End_Date))/7)*7) - (DAYS(Begin_Date)- INTEGER((DAYS(Begin_Date))/7)*7) END)
The formula first calculates the number of whole weeks and adds five days for every whole week. One day is added to this calculation because this is for class length and a class that begins and ends on the same day has a length of one day. The rest of the calculation gets left over days after whole weeks are taken out from the begin and end dates and compares them to see whether a weekend has passed between the days and adjusts the amount added to the total accordingly.
On Wed, 28 Feb 2001 15:46:28 -0500, Ted Michalski tmichal1@FORD.COM wrote:
Here’s a good one. I need to calculate the number of days between two dates. Normally not a problem… but I don’t want to count weekends in the calculation. So if I wanted to get the number of days between today (Wednesday) and last Tuesday, I need to result to be 6 (8 total days - the two weekend days). Has anyone ever had to do this?
Listserv Archives (BOB member since 2002-06-25)
In a message dated Thu, 1 Mar 2001 7:01:49 AM Eastern Standard Time, Ted Michalski tmichal1@FORD.COM writes:
<< Here’s a good one. I need to calculate the number of days between two dates. Normally not a problem… but I don’t want to count weekends in the calculation. So if I wanted to get the number of days between today (Wednesday) and last Tuesday, I need to result to be 6 (8 total days - the two weekend days). Has anyone ever had to do this?
Here’s something to get you started: it’s not perfect, but you should be able to take the idea and run with it.
=(Week() - Week()) * 5) + Mod(DaysBetween(, ), 7)
The Week() function returns the week number of a date. So in your example of Wednesday this week and Tuesday last week, the week could be 10 and 9; 10-9 is 1, times 5 is five days for the included “week’s” time.
The DaysBetween(, ) returns 8. Mod (8, 7) returns 1, or one additional day. So five days (for the one week) plus 1 additional day = 6 days.
Looks good so far… why did I say it was a starting point? Because you need to handle the special situation where date 2 is in a different year from date 1. If date 2 is Jan 4, and date 1 is Dec 30, then the week numbers may be 1 and 52 (or 53). You need to adjust accordingly.
Regards,
Dave Rathbun
Integra Solutions
www.islink.com
Listserv Archives (BOB member since 2002-06-25)
We have a period table with a row for every day for the past 20 years & goes forward to the year 2010. We added a column with a running count for business day. One of our columns indicated whether it was a holiday & another column was the day of the week. So the column we added started at 1 & increased by 1 if the next row had no holiday or Saturday/Sunday indicator. Then, in designer we aliased this table & made the joins on the date fields we needed to calculate from. You end up with a simple plus/minus calculation that works across different months & years.
Here’s a good one. I need to calculate the number of days between two dates. Normally not a problem… but I don’t want to count weekends in the calculation. So if I wanted to get the number of days between today (Wednesday) and last Tuesday, I need to result to be 6 (8 total days - the two weekend days). Has anyone ever had to do this?
Listserv Archives (BOB member since 2002-06-25)
Mathematically the solution could be
Div(DaysBetween(,),7) * 5 + f[Mod(DaysBetween(,),7)]
where f is a function that deals with starting and/or ending on weekends. (Here’s me pretending I know maths 
)
Gotta have a few rules before setting out on this quest. Let’s say no timestamps - midnights only - so if the dates are the same day no days have passed. And date2 > date1 for simplicity. Given this we can get something to work as a variable in reports:
=(Floor(()/7)*5)+(If (If
DayNumberOfWeek()=DayNumberOfWeek() Then 0 Else If DayNumberOfWeek()=6 Then (If DayNumberOfWeek()=7 Then 0 Else Mod( ,7)-2) Else If DayNumberOfWeek()=7 Then Mod( ,7)-1 Else Mod( ,7))=6 Then 5 Else (If DayNumberOfWeek()=DayNumberOfWeek() Then 0 Else If DayNumberOfWeek()=6 Then (If DayNumberOfWeek()=7 Then 0 Else Mod( ,7)-2) Else If DayNumberOfWeek()=7 Then Mod( ,7)-1 Else Mod( ,7)))
Where the variable is DaysBetween(,).
This actually translates into a simpler looking piece of Oracle SQL which I suppose could be used in an object in very simple, non performance intensive reports
(FLOOR((date2-date1)/7)*5)+DECODE(DECODE(to_char(date1,‘D’),to_char(date2,'D '),0,7,DECODE(to_char(date2,‘D’),1,0,MOD(date2-date1,7)-2),1,MOD(date2-date1 ,7)-1,MOD(date2-date1,7)),6,5,DECODE(to_char(date1,‘D’),to_char(date2,‘D’),0 ,7,DECODE(to_char(date2,‘D’),1,0,MOD(date2-date1,7)-2),1,MOD(date2-date1,7)- 1,MOD(date2-date1,7)))
And at the end of all this work, it would still be easier still to have a lookup table with flags for weekdays, which of course could take care of holidays. And I have no doubt there are briefer ways of dealing with the logic, so I’m looking for them.
Thanks for the puzzle,
Paul
From: DRathbun@AOL.COM [SMTP:DRathbun@AOL.COM] Sent: Friday, March 02, 2001 4:02 AM
In a message dated Thu, 1 Mar 2001 7:01:49 AM Eastern Standard Time, Ted Michalski tmichal1@FORD.COM writes:
<< Here’s a good one. I need to calculate the number of days between two dates. Normally not a problem… but I don’t want to count weekends in the calculation. So if I wanted to get the number of days between today (Wednesday) and last Tuesday, I need to result to be 6 (8 total days - the two weekend days). Has anyone ever had to do this?
Here’s something to get you started: it’s not perfect, but you should be able to take the idea and run with it.
=(Week() - Week()) * 5) + Mod(DaysBetween(, ), 7)
The Week() function returns the week number of a date. So in your example of Wednesday this week and Tuesday last week, the week could be 10 and 9; 10-9 is 1, times 5 is five days for the included “week’s” time.
The DaysBetween(, ) returns 8. Mod (8, 7) returns 1, or one additional day. So five days (for the one week) plus 1 additional day = 6 days.
Looks good so far… why did I say it was a starting point? Because you need to handle the special situation where date 2 is in a different year from date 1. If date 2 is Jan 4, and date 1 is Dec 30, then the week numbers may be 1 and 52 (or 53). You need to adjust accordingly.
Listserv Archives (BOB member since 2002-06-25)
This post may help:
David P. 
(BOB member since 2010-11-09)