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
<7 Working Days> =DaysBetween(<Month> ,<Last Day of month>)+1kool 
(BOB member since 2005-04-15)
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…)
hagnik 
(BOB member since 2006-08-01)
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.
Steps
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.
kool 
(BOB member since 2005-04-15)
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.
Marek Chladny 
(BOB member since 2003-11-27)
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.
kool (BOB member since 2005-04-15)
How are you calculating the number of weekends?
mkumar (BOB member since 2002-08-26)
FYI,
=(Truncate((DayNumberOfWeek(<Month>)+<7 working days>)/7 ,0))*2Where <7 working days> is number of days in a month.
kool (BOB member since 2005-04-15)
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 …!!!
kool 
(BOB member since 2005-04-15)
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]
mkumar (BOB member since 2002-08-26)
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?
BO_Chief 
(BOB member since 2004-06-06)
Here weekends include number of Saturday and Sunday in a month in order to calculate 5 working days.
you are correct mkumar ; Sept, Oct and Jan have 9 weekends and they are showing wrong working days.
kool (BOB member since 2005-04-15)
Update :-
The follwing code helped me to calculate the correct working days.
=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.
kool (BOB member since 2005-04-15)
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.
<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>
mkumar (BOB member since 2002-08-26)
Forgot to mention that is the first date of each month.
mkumar (BOB member since 2002-08-26)
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 .
<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 >kool (BOB member since 2005-04-15)
Does this formula return you correct values (for Nov and Dec 06)?
mkumar (BOB member since 2002-08-26)
I think it is !
kool (BOB member since 2005-04-15)
Kool
I got a little intrigued 
.
See what i accomplished so far:
Got from data provider:
Month name
<FIRST_DATE> is first date of each month
Create these variables:
<Last Day> = LastDayOfMonth(<FIRST_DATE>)
<Day> = =DayNumberOfWeek(<Last Day>)
<Month Days> = =DaysBetween(<FIRST_DATE> ,LastDayOfMonth(<FIRST_DATE>))+1
<Diff 6> = 4 + If <Day> InList (1 , 7) Then 1 Else If <Day> = 2 Then If <Month Days>=31 Then 1 Else 0 Else 0 As a month can be 28+0/1/2/3 days it will always have 4 full weeks so first 4 holidays are set when we have 6 working days,
also we know that in the following +1/2/3 days we can have additional holidays
<Week End 6> = <Month Days>-<Diff 6>Te same logic but a litle more complicated goes in case of 5 working days
Even here we have a set of 28 days per month which asures us an 8 holidays
for the other 3 possible days we will check using three variables.
<Day 30 31 5> = <Month Days>-30 Check if the month has 31 days if not will be 0 or negative.
<Day 7-30 5>=Floor(<Day>/6)*Mod((<Month Days>-30)+1 ,2)Check if the last day of month falls on the last weekend and if the month has 31 days
<Day Odd 5> = If Mod(<Day> ,6) = 1 Then 1 Else 0Check if the Last day of month falls in day of week 1,7. it means we will have 2 working days
<Diff 5> = If <Month Days> = 28 Then 0 Else If <Month Days> = 29 Then If <Day> InList(6 ,7) Then 1 Else 0 Else If <Day> InList(1 ,2 ,6 ,7) Then <Day 30 31 5>+<Day 7-30 5>+<Day Odd 5> Else 0Last checking for 28-29 February before giving the value
<Week End 6>=<Month Days>-8-<Diff 5>Your requirement for 5 working days. Also Note that again we have 8 fixed holidays from 4 full weeks.
See also attached the jpg and the report
test.rep (60.0 KB)
AldoWeb 
(BOB member since 2005-05-24)
If one person works 5 days, and another works 6 days, then you need to know which days they won’t be working … for a 5 day week, this is typically going to be Saturday and Sunday and for the 6-day employee, probably Sunday. But can you be sure of this? Will they be hardcoded?
If not, the only way is to have a calendar table that gives only the non-working days. Rows can then be inserted as necessary - say the table is populated for 2 years at a time.
Let’s assume Saturday and Sunday so we can do without a calendar table. This SQL is in Oracle SQL:
select last_day('01-OCT-2006') - to_date('01-OCT-2006') + 1 -
decode(to_char(last_day('01-OCT-2006'), 'DY'), 'SUN', 10, 'SAT', 9, 8)
from dual
/ This will yield 23 working days in October 2006, assuming no work for Saturdays and Sundays.
I’m supplying a hardcoded date to give the idea - clearly you’d give a variable (date with the time element stripped off). Ideally, this will be the first day of the month, but if not it will give the number of working days remaining until the end of the month.
Angoid 
(BOB member since 2004-11-30)
Just a correction …
I was looking into this code and comparing… the month of Oct’2006…(5 sundays, 4 saturdays), The above code returns 23, whereas it has 22 working days if we assume SAT and SUN as non-working days…
Am I missing something ?
BO_Chief (BOB member since 2004-06-06)