count specific weedays remaining in quarter

[Green Fox]

Our quarter runs September through November, I need to count the
number of Tuesdays, Wednesdays etc., remaining in the quarter. I've
come close by digging around in these forums but I'm stumped.

Remaining
Q1 F2008 F2008
Sunday 13 3
Monday 13 3
Tuesday 13 3
Wednesday 13 3
Thursday 13 3
Friday 13 3
Saturday 13 3


There are 13 of each weekday in Quarter 1.

I've cobbled together this formula to calculate the remaining days:

=P18-(INT((WEEKDAY($V$23-1,1)+$V$24-$V$23)/7))

P18 is the total number of Sundays in the quarter
$V$23 is the date: 1-sep-07 (start of the quarter)
$V$24 is the date: today()

I know that:

=WEEKDAY($V23,1)

will give me the Weekday number, but I don't know how to integrate a
count function into this to establish the remaining weekdays.

I've tried taking out the -1 but it didn't seem to affect anything.

If any one can point me in the right direction, I would appreciate it.

Andy

 

[Ron Coderre]

With
A2: (QtrStartDate)
B2: (QtrEndDate)

These formulas returns the number of...

Mondays in that date range:
=SUM(INT((WEEKDAY($A$2-2)+$B$2-$A$2)/7))

Tuesdays in that date range:
=SUM(INT((WEEKDAY($A$2-3)+$B$2-$A$2)/7))

etc

Note: the value after $A$2 in the WEEKDAY section is:
1=Sun, 2=Mon, 3=Tue, 4=Wed, 5=Thu, 6=Fri, 7=Sat

Does that help?
Post back if you have more questions.
--------------------------

Regards,

Ron (XL2003, Win XP)
Microsoft MVP (Excel)

 

[Guest]

Hmmm....It would have been nice if I'd read the rest of your post, don't you
think?

OK....Now that I've finished my coffee AND read your entire post...

Try this:
With
A2: (QtrStartDate)
B2: (QtrEndDate)
V23: (a date)
C3: Totals
D3: Remaining

B4: Mondays
C4: =SUM(INT((WEEKDAY($A$2-2)+$B$2-$A$2)/7))
D4: =SUM(INT((WEEKDAY($V$23-2)+$B$2-$V$23)/7))

Does that help?
***********
Regards,
Ron

XL2003, WinXP

 

[Guest]

A1: QtrEndDate

A2: =WEEKNUM(A1)-WEEKNUM(TODAY())

 

[Green Fox]

Thanks Ron & Teethless

It's working perfectly. I appreciate the help,




That worked perfectly