BOB: Business Objects Board
Not endorsed by or affiliated with SAP

Register | Login 

 
Lead By Knowing (Opens a new window)  

General Notice: No events within the next 45 days.

Calculate Working days in a month
1 members found this topic helpful
Goto page 1, 2  Next
 
Search this topic... | Search Desktop Intelligence... | Search Box
Register or Login to Post    Forum Index -> Building Reports -> Desktop Intelligence  Previous TopicPrint TopicNext Topic
Author Message
kool
Principal Member
Principal Member



Joined: 15 Apr 2005
ASUG Icon
Posts: 340


flag
PostPosted: 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
Senior Member



Joined: 01 Aug 2006

Posts: 92
Location: Columbus Ohio


flag
PostPosted: 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
Principal Member



Joined: 15 Apr 2005
ASUG Icon
Posts: 340


flag
PostPosted: 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
Forum Advocate



Joined: 27 Nov 2003

Posts: 17761
Location: Bratislava


flag
PostPosted: 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 non-weekend days.

Let's say if Christmas (25th and 26th of December) were holidays (non-working 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.1 | XI 3, 3.1 | XI r2 | 6.x | 5.x
DB: Oracle, MS SQL Server, DB2, Teradata, Netezza
HW: Win and Linux servers
www.perpendulum.com

Latest Blog Posts
• 2013-07-16 Number of reports in a WebI document
• 2013-04-01 Optional prompts in a universe
• 2012-06-08 Calendar table script for Oracle
Back to top
kool
Principal Member
Principal Member



Joined: 15 Apr 2005
ASUG Icon
Posts: 340


flag
PostPosted: 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 non-weekend days.

Let's say if Christmas (25th and 26th of December) were holidays (non-working 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
Forum Associate



Joined: 26 Aug 2002

Posts: 770
Location: Manhattan, NY



PostPosted: 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
Principal Member



Joined: 15 Apr 2005
ASUG Icon
Posts: 340


flag
PostPosted: 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
Principal Member



Joined: 15 Apr 2005
ASUG Icon
Posts: 340


flag
PostPosted: 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
Forum Associate



Joined: 26 Aug 2002

Posts: 770
Location: Manhattan, NY



PostPosted: 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
Forum Fanatic



Joined: 06 Jun 2004
ASUG Icon
Posts: 5524
Location: Somewhere on God's Land.


flag
PostPosted: 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
Principal Member



Joined: 15 Apr 2005
ASUG Icon
Posts: 340


flag
PostPosted: 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 ? lol.gif

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
Principal Member



Joined: 15 Apr 2005
ASUG Icon
Posts: 340


flag
PostPosted: 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
Forum Associate



Joined: 26 Aug 2002

Posts: 770
Location: Manhattan, NY



PostPosted: 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
Forum Associate



Joined: 26 Aug 2002

Posts: 770
Location: Manhattan, NY



PostPosted: 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
Principal Member



Joined: 15 Apr 2005
ASUG Icon
Posts: 340


flag
PostPosted: 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
Display posts from previous:   
Register or Login to Post    Forum Index -> Building Reports -> Desktop Intelligence  Previous TopicPrint TopicNext Topic
Page 1 of 2 All times are GMT - 5 Hours
Goto page 1, 2  Next
 
Jump to:  

Index | About | FAQ | RAG | Privacy | Search |  Register |  Login 

Get community updates via Twitter:

Not endorsed by or affiliated with SAP
Powered by phpBB © phpBB Group
Generated in 0.0508 seconds using 17 queries. (SQL 0.0033 Parse 0.0356 Other 0.0120)
CCBot/2.0 (http://commoncrawl.org/faq/)
Hosted by ForumTopics.com | Terms of Service
phpBB Customizations by the phpBBDoctor.com
Shameless plug for MomentsOfLight.com Moments of Light Logo