| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
count specific weedays remaining in quarter
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 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
count specific weedays remaining in quarter
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) "Green Fox" wrote in message ups.com... 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 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
count specific weedays remaining in quarter
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 "Ron Coderre" wrote: 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) "Green Fox" wrote in message ups.com... 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 |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
count specific weedays remaining in quarter
A1: QtrEndDate
A2: =WEEKNUM(A1)-WEEKNUM(TODAY()) "Green Fox" wrote: 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 |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
count specific weedays remaining in quarter
Thanks Ron & Teethless It's working perfectly. I appreciate the help, That worked perfectly On Nov 7, 9:27 am, Teethless mama wrote: A1: QtrEndDate A2: =WEEKNUM(A1)-WEEKNUM(TODAY()) "Green Fox" wrote: 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- Hide quoted text - - Show quoted text - |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How do I convert a specific date to a fiscal quarter ? | Excel Discussion (Misc queries) | |||
| Count of days in a quarter | Excel Discussion (Misc queries) | |||
| Count workdays remaining in a month | Excel Discussion (Misc queries) | |||
| Count Specific word in specific range | Excel Worksheet Functions | |||
| Count If Specific word in specific range | Excel Discussion (Misc queries) | |||
Linear Mode
