counting days vers 2

[Guest]

First of all, thanks to everyone who posted reponses to my last request to figure out how to count the number of selling days so far in a month to figure out sales targets. I would like to expand on that a little bit

1. How do I figure out the total number of selling days for the month (minus holidays) so that I can use a formula like =sales target/# of selling days for the month)*# of selling days so far in month? The number of selling days so far I can figure out
2. How do I figure out both the number of selling days for a quarter (say april to june) and the number of selling days so far in that quarter? so that I can apply the same formula but for quarter targets instead of monthly targets?

 

[Andy B]

Hi

I haven't seen your previous post, but I would suggest using NETWORKDAYS()
which is part of the Analysis ToolPack Add-In

This formula:
=NETWORKDAYS(TODAY()-DAY(TODAY())+1,DATE(YEAR(TODAY()),MONTH(TODAY())+1,0))
will return the number of working days for the current month, not counting
weekends. You can include a holiday list, too, if necessary. See help for
more info.

You can use the various parameters of NETWORKDAYS to work out your quarterly
days, and the number of days gone/to go.
Quarter 1 =NETWORKDAYS("01/01/04","31/03/04")
Quarter 2 =NETWORKDAYS("01/04/04","30/06/04")
Quarter 3 =NETWORKDAYS("01/07/04","30/09/04")
Quarter 4 =NETWORKDAYS("01/10/04","31/12/04")

By the same method as above, you can work out how many days are yet to go

Hope this helps.
--
Andy.

First of all, thanks to everyone who posted reponses to my last request to

figure out how to count the number of selling days so far in a month to
figure out sales targets. I would like to expand on that a little bit.

1. How do I figure out the total number of selling days for the month

(minus holidays) so that I can use a formula like =sales target/# of selling
days for the month)*# of selling days so far in month? The number of
selling days so far I can figure out.

2. How do I figure out both the number of selling days for a quarter (say

april to june) and the number of selling days so far in that quarter? so
that I can apply the same formula but for quarter targets instead of monthly
targets?

 

[Guest]

I would like to include saturdays but not holidays. I already have a range for holidays. I'm using the following formula to calculate the number of selling days so far

=(1+TODAY()-DATE(YEAR(TODAY()),MONTH(TODAY()),1)-INT((TODAY()-WEEKDAY(TODAY())-DATE(YEAR(TODAY()),MONTH(TODAY()),1)+8)/7)-SUMPRODUCT((Holidays>=DATE(YEAR(TODAY()),MONTH(TODAY()),1))*(Holidays<=TODAY())*(WEEKDAY(Holidays)<>1))

I admit I don't fully understand the formula so I can't maniputate it to count the days of the quarter or to total days of the month/or quarter
----- Andy B wrote: ----

H

I haven't seen your previous post, but I would suggest using NETWORKDAYS(
which is part of the Analysis ToolPack Add-I

This formula
=NETWORKDAYS(TODAY()-DAY(TODAY())+1,DATE(YEAR(TODAY()),MONTH(TODAY())+1,0)
will return the number of working days for the current month, not countin
weekends. You can include a holiday list, too, if necessary. See help fo
more info

You can use the various parameters of NETWORKDAYS to work out your quarterl
days, and the number of days gone/to go
Quarter 1 =NETWORKDAYS("01/01/04","31/03/04"
Quarter 2 =NETWORKDAYS("01/04/04","30/06/04"
Quarter 3 =NETWORKDAYS("01/07/04","30/09/04"
Quarter 4 =NETWORKDAYS("01/10/04","31/12/04"

By the same method as above, you can work out how many days are yet to g

Hope this helps
--
Andy

First of all, thanks to everyone who posted reponses to my last request t

figure out how to count the number of selling days so far in a month t
figure out sales targets. I would like to expand on that a little bit(minus holidays) so that I can use a formula like =sales target/# of sellin
days for the month)*# of selling days so far in month? The number o
selling days so far I can figure out

2. How do I figure out both the number of selling days for a quarter (sa

april to june) and the number of selling days so far in that quarter? s
that I can apply the same formula but for quarter targets instead of monthl
targets

 

[Daniel.M]

Hello Brad,

So:
A1=Today() ' because current month
A2:=EOMONTH(A1,-1)+1
A3:=EOMONTH(A1,0)
A4: The formula to calculate the number of working days (as per your specs)
between A2 and A3 is :
=NETWORKDAYS(A2,A3,Holidays)
+INT((A3-WEEKDAY(A3-6)-A2+8)/7)
-SUMPRODUCT((WEEKDAY(Holidays)=7)*(Holidays>=A2)*(Holidays<=A3))

As you can see, networkdays() does most of the job but you need to add the
saturdays and substract the saturdays that are Holidays.

If you need for current quarter, in
B2: =DATE(YEAR(A1),3*INT((MONTH(A1)-1)/3)+1,1)
B3: =DATE(YEAR(A1),3*INT((MONTH(A1)-1)/3)+4,0)
Copy A4 to B4. B4 contains your answer

For your initial problem,
C2: =EOMONTH(A1,-1)+1
C3: =A1
Copy B4 to C4. C4 now contains your answer

Regards,

Daniel M.

I would like to include saturdays but not holidays. I already have a

range for holidays. I'm using the following formula to calculate the number of
selling days so far:=(1+TODAY()-DATE(YEAR(TODAY()),MONTH(TODAY()),1)-INT((TODAY()-WEEKDAY(TODAY())-D
ATE(YEAR(TODAY()),MONTH(TODAY()),1)+8)/7)-SUMPRODUCT((Holidays>=DATE(YEAR(TODAY(

I admit I don't fully understand the formula so I can't maniputate it to count

the days of the quarter or to total days of the month/or quarter.