Thread: days within the month

Hi
Gurus

Please advic eme how can i get the no of
1.business days in a month and
2.elapsed days

where if i can fill in the holidays in another column

referring to the holidays column i must be able to achive the above two

thanks for your great help earlier and in advance

Hi thanks for the repsonse

but my requirement is to find the no of business days in the particular month only.
sorry i was not specific in mypost

it should be based on the month drop down selected
if the the user selects may then it must show
1.the number of business days in the may month
2. and if the previous month selected for example may then the elapsed days will be equal to the business days in the may month
3. if june is selected then assuming that today is the 29th june
a. business days must be 22
b. elapsed days must be 21
4. if may is selected when the current month is june , then it must never show the days in june but must show only may month business days

hope it is not confusing

thanks

Originally Posted by daddylonglegs

So what exactly does the dropdown show? A date? just a month like "December" or a month and year......

there are three drop downs which are necessary for my kind of report where the user must be able to choose all the three seperately

drop down with a year 2011
drop down with a month APR
drop down with a day 01



thanks

Originally Posted by daddylonglegs

Assuming A1 is year, B1 is month and C1 is day then to get total business days in that whole month try this formula

=NETWORKDAYS(EOMONTH(C1&B1&A1,-1),EOMONTH(C1&B1&A1,0),holidays)

I'm not sure what you want for elapsed days. What if 4th Jun 2011 is selected - do you want business days from then to now? (or will the day only be 1)?

What about previous dates (or future dates)?

if june 4th is selected
since today is june 30th
the number of elapsed days will be aall the business days from 4th june to 29th june that is excluding today() which will be total elapsed days 18

thanks

Originally Posted by daddylonglegs

Assuming A1 is year, B1 is month and C1 is day then to get total business days in that whole month try this formula

=NETWORKDAYS(EOMONTH(C1&B1&A1,-1),EOMONTH(C1&B1&A1,0),holidays)

I'm not sure what you want for elapsed days. What if 4th Jun 2011 is selected - do you want business days from then to now? (or will the day only be 1)?

What about previous dates (or future dates)?

when i try the above formula for no of bus days in the month say for example APR
we have 21 working days but withthe formula i am getting 22 working days

please advice

Originally Posted by daddylonglegs

Sorry - I missed out a +1 which means that the count included the last day of the previous month - should be

=NETWORKDAYS(EOMONTH(C1&B1&A1,-1)+1,EOMONTH(C1&B1&A1,0),holidays)

and for elapsed days

=MAX(0,NETWORKDAYS(MAX(C1&B1&A1,EOMONTH(TODAY(),-1)+1),TODAY()-1,holidays))

I donot want the holidays to be considered
if we donot consider the holidays
my formula would be
MAX(0,NETWORKDAYS(MAX($AL$64&$AL$63&$AL$62,EOMONTH(TODAY(),-1)+1),TODAY()-1))

but the result for the month of june is 18 days

am i doing something wrong ?

the earlier formula for no of business days in a month works perfectly

thanks

Originally Posted by daddylonglegs

Are you saying that you get 18 when you shouldn't or you want to get 18 and you don't? For which dates, what's in $AL$64, and $AL$63 and $AL$62?

the formula to calculate elapsed days (not counting today) is
MAX(0,NETWORKDAYS(MAX(C1&B1&A1,EOMONTH(TODAY(),-1)+1),TODAY()-1))

results only 18 for the month of jun

which should actually result as 21 if not counting today
it should result in 21 days
sorry if my question was not clear in the previous post

thanks