Author 
Message 
kool Principal Member
Joined: 15 Apr 2005
Posts: 340

Posted: Mon Nov 06, 2006 10:59 am Post subject: Calculate Working days in a month 


How do I calculate number of working days in a month?
If an employee works for 5 days in a week, how many days he will be working in a month?
Similarly, if he works for 6 days in a week, how many days he will be working in a month?
Similarly, if he works for 7 days in a week, how many days he will be working in a month?
So far I am able to calculate 7 working days
Code:  <7 Working Days> =DaysBetween(<Month> ,<Last Day of month>)+1 
_________________ "Impossible is nothing" Adidas 

Back to top 


hagnik Senior Member
Joined: 01 Aug 2006
Posts: 92 Location: Columbus Ohio

Posted: Mon Nov 06, 2006 11:37 am Post subject: Re: Calculate Working days in a month 


I suspect without a calendar table, you will not be able to get the exact number or working days b/c each month has a different number of each day (Mon, Tues....) 

Back to top 


kool Principal Member
Joined: 15 Apr 2005
Posts: 340

Posted: Tue Nov 07, 2006 10:21 am Post subject: Re: Calculate Working days in a month 


hagnik wrote:  I suspect without a calendar table, you will not be able to get the exact number or working days b/c each month has a different number of each day (Mon, Tues....) 
You are correct that every month has different number of days but i am confidence this can be resolved without needing to create calender table in database.
Here is the latest updates regarding this issue
Number of working days in a month if an employee works for 5 days in a week. <Working Days>
Steps
1) Calculate number of days in a month
2) Calculate number of weekends in a month
3) <WorkingDays> = Floor(number of working days in a month  number of weekends in a month )
The above calculation is correct for most of the months but some. For instance, Sept 2006 should have 21 working days but the calculation shows only 20 working days.
Any suggestion or idea is very helpful to me. _________________ "Impossible is nothing" Adidas 

Back to top 


Marek Chladny Forum Advocate
Joined: 27 Nov 2003
Posts: 18758 Location: Bratislava

Posted: Tue Nov 07, 2006 10:49 am Post subject: Re: Calculate Working days in a month 


Hi,
the problem of this approach (without using smart calendar table) is that you will never be able to take into account the holidays that fall into nonweekend days.
Let's say if Christmas (25th and 26th of December) were holidays (nonworking days) and would be on Tuesday and Wednesday (just an example) then your calculation logic would treat these days as normal working days.
Just my $0.02. _________________ BO: BI 4.0, 4.1  XI 3, 3.1  XI r2  6.x  5.x
DB: Oracle, MS SQL Server, DB2, Teradata, Netezza
HW: Win, Linux and AIX servers
www.perpendulum.com
Latest Blog Posts • 20130716 Number of reports in a WebI document • 20130401 Optional prompts in a universe • 20120608 Calendar table script for Oracle 

Back to top 


kool Principal Member
Joined: 15 Apr 2005
Posts: 340

Posted: Tue Nov 07, 2006 11:20 am Post subject: Re: Calculate Working days in a month 


Marek Chladny wrote:  Hi,
the problem of this approach (without using smart calendar table) is that you will never be able to take into account the holidays that fall into nonweekend days.
Let's say if Christmas (25th and 26th of December) were holidays (nonworking days) and would be on Tuesday and Wednesday (just an example) then your calculation logic would treat these days as normal working days.
Just my $0.02. 
Thanks for your inputs Marek. But we are not interested in public holidays. All we want to see is number of working days in a month regardless of public holidays. _________________ "Impossible is nothing" Adidas 

Back to top 


mkumar Forum Associate
Joined: 26 Aug 2002
Posts: 771 Location: Manhattan, NY

Posted: Tue Nov 07, 2006 12:40 pm Post subject: Re: Calculate Working days in a month 


Quote:  2) Calculate number of weekends in a month 
How are you calculating the number of weekends? 

Back to top 


kool Principal Member
Joined: 15 Apr 2005
Posts: 340

Posted: Tue Nov 07, 2006 3:45 pm Post subject: Re: Calculate Working days in a month 


mkumar wrote:  Quote:  2) Calculate number of weekends in a month 
How are you calculating the number of weekends? 
FYI,
Code:  =(Truncate((DayNumberOfWeek(<Month>)+<7 working days>)/7 ,0))*2 
Where <7 working days> is number of days in a month. _________________ "Impossible is nothing" Adidas 

Back to top 


kool Principal Member
Joined: 15 Apr 2005
Posts: 340

Posted: Tue Nov 07, 2006 4:41 pm Post subject: Re: Calculate Working days in a month 


Ok, Here is the update on what i have accomplished so far.
If you take a look at picture you will see red in Jan, Sept, and Oct months. These three months are producing wrong working days.
The similarity among these three months are; either starting day of the month or ending day of the month falls on weekends. But my logic may not be true because December and April months also end on weekend and they are showing correct working days.
Any help ...!!!! _________________ "Impossible is nothing" Adidas 

Back to top 


mkumar Forum Associate
Joined: 26 Aug 2002
Posts: 771 Location: Manhattan, NY

Posted: Wed Nov 08, 2006 5:14 pm Post subject: Re: Calculate Working days in a month 


kool wrote:  FYI,
Code:  =(Truncate((DayNumberOfWeek(<Month>)+<7 working days>)/7 ,0))*2 
Where <7 working days> is number of days in a month. 
This logic is not correct. This will always give you a even number where as there could be 8, 9 or 10 weekends in a month.
You will need to put in extra logic to identify if there are 9 weekends in a month. [/code] 

Back to top 


BO_Chief Forum Fanatic
Joined: 06 Jun 2004
Posts: 5585 Location: Somewhere on God's Land.

Posted: Wed Nov 08, 2006 5:32 pm Post subject: Re: Calculate Working days in a month 


mkumar wrote:  This will always give you a even number where as there could be 8, 9 or 10 weekends in a month.
You will need to put in extra logic to identify if there are 9 weekends in a month. 
Hi,
Sorry, I have a doubt ??
Can you tell me in which month you will have 8, 9 or 10 weekends ?
I want to know.. are we talking here Gregorian Calendar or any other? _________________ help us help you! make sure your post has the following elements:
• Does it include BO version, Database, an error, a problem, a SQL for object or Condition ?
• Does it include some sample data what you have?
• Does it include any code you already tried ? (working or not..)
• Did you explain what you want for results?
If any of those elements are missing,chances are you didn't post enough information for us to help you!
Last edited by BO_Chief on Wed Nov 08, 2006 6:26 pm, edited 1 time in total 

Back to top 


kool Principal Member
Joined: 15 Apr 2005
Posts: 340

Posted: Wed Nov 08, 2006 6:09 pm Post subject: Re: Calculate Working days in a month 


BO_Chief wrote:  mkumar wrote:  This will always give you a even number where as there could be 8, 9 or 10 weekends in a month.
You will need to put in extra logic to identify if there are 9 weekends in a month. 
Hi,
Sorry, I have a doubt ??
Can you tell me in which month you will have 8, 9 or 10 weekends ?
I want to know.. are we talking here Gregorian Calendar or any other? 
Here weekends include number of Saturday and Sunday in a month in order to calculate 5 working days.
Quote:  You will need to put in extra logic to identify if there are 9 weekends in a month 
you are correct mkumar ; Sept, Oct and Jan have 9 weekends and they are showing wrong working days. _________________ "Impossible is nothing" Adidas 

Back to top 


kool Principal Member
Joined: 15 Apr 2005
Posts: 340

Posted: Fri Nov 10, 2006 10:51 am Post subject: Re: Calculate Working days in a month 


Update :
The follwing code helped me to calculate the correct working days.
Code:  =If <first day of month > = 7 Or <Last day of last week > =6 Then (<5 Working Days >+1) Else <5 Working Days > 
mkumar, please let me know if my logic is still wrong. _________________ "Impossible is nothing" Adidas 

Back to top 


mkumar Forum Associate
Joined: 26 Aug 2002
Posts: 771 Location: Manhattan, NY

Posted: Fri Nov 10, 2006 1:29 pm Post subject: Re: Calculate Working days in a month 


I figured this needs some step by step calculations.
Here is the variables I had to create to get the correct values for weekends and working days for 5 day weeks.
Code: 
<First Working Day Number> = DayNumberOfWeek(<Date>)
<Last Working Day Number> = DayNumberOfWeek(LastDayOfMonth(<Date>))
<Start Day> = If <First Working Day Number> = 7 Then 2 Else If <First Working Day Number> = 6 Then 3 Else 1
<End Day> = If <Last Working Day Number> = 7 Then DayNumberOfMonth(LastDayOfMonth(<Date>))  1 Else If <Last Working Day Number> = 6 Then DayNumberOfMonth(LastDayOfMonth(<Date>))  2 Else DayNumberOfMonth(LastDayOfMonth(<Date>))
<Weekends 1> = If <First Working Day Number> = 7 Then 1 Else If <First Working Day Number> = 6 Then 2 Else 0
<Weekends 2> = If <Last Working Day Number> = 6 Then 1 Else If <Last Working Day Number> = 7 Then 2 Else 0
<Weekends 3> = Truncate((<End Day>  <Start Day>+1)/7 ,0)*2
<Total Weekends> = <Weekends 1>+<Weekends 2>+<Weekends 3>
<Total 5 Working Days> = DayNumberOfMonth(LastDayOfMonth(<Date>))  <Total Weekends>



Back to top 


mkumar Forum Associate
Joined: 26 Aug 2002
Posts: 771 Location: Manhattan, NY

Posted: Fri Nov 10, 2006 1:39 pm Post subject: Re: Calculate Working days in a month 


Forgot to mention that <Date> is the first date of each month. 

Back to top 


kool Principal Member
Joined: 15 Apr 2005
Posts: 340

Posted: Sun Nov 12, 2006 2:44 pm Post subject: Re: Calculate Working days in a month 


Thanks mkumar,
This is what i am currently doing to calculate number of weekends (5 working days) in a month. This involves less if else statement .
Code:  <last day of month> =LastDayOfMonth(< Month>)
<Last day of last week> =DayNumberOfWeek(<last day of month >)
<first day of month> =DayNumberOfWeek(< Month>)
<days in a month> ==DaysBetween(< Month> ,<last day of month >)+1
<weekends> =(Truncate((DayNumberOfWeek(< Month>)+<Days in a month >)/7 ,0))
<Working Days> =Floor(<7 Working Days >(<number of weekends>)*2)
<5 working Days> =If <first day of month > = 7 Or <Last day of last week > =6 Then (< Working Days >+1) Else < Working Days > 
_________________ "Impossible is nothing" Adidas 

Back to top 


