Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
number of specific weekdays
I undertake trainings on sundays, wednesdays and Thursdays.
Is there a way that I can get the number of sundays, tuesdays and wednesdays from January 2009 to September 30 2009 I use excel 2007 Thanking you Afd |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
number of specific weekdays
Hi Afd,
Supposing that you have a sheet with all days from 01/01/2009 till 30/09/2009. ={SUM(--(WEEKDAY(A1:A400)=1))} Should count all Sundays in the range. Use 3 for Tuesdays and 5 for Thursdays. You have to enter this function as an array function, (control-shift -enter, do not include the brackets). Wkr, JP "afdmello" wrote in message ... I undertake trainings on sundays, wednesdays and Thursdays. Is there a way that I can get the number of sundays, tuesdays and wednesdays from January 2009 to September 30 2009 I use excel 2007 Thanking you Afd |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
number of specific weekdays
On Sun, 18 Oct 2009 17:23:14 +0300, "afdmello"
wrote: I undertake trainings on sundays, wednesdays and Thursdays. Is there a way that I can get the number of sundays, tuesdays and wednesdays from January 2009 to September 30 2009 I use excel 2007 Thanking you Afd If your (inclusive) start date is in cell A1 and your (includive) end date is in cell A2, try the following formula: =SUMPRODUCT(LOOKUP(WEEKDAY(A$1+ROW(OFFSET(A$1,,,A$ 2-A$1+1))),ROW($1:$7),{1,0,1,0,1,0,0})) The {1,0,1,0,1,0,0} at the end of the formula is a vector representing Sunday, Monday, ..., Saturday where I have put a 1 for those weekdays to be counted and a 0 for the others. Hope this helps / Lars-Åke |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
number of specific weekdays
Hi,
Try this. C6 has 1/1/2009 and C7 has 30/9/2009 =SUMPRODUCT(1*(WEEKDAY(ROW(INDIRECT(C6&":"&C7)))={ 1,3,4})) -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "afdmello" wrote in message ... I undertake trainings on sundays, wednesdays and Thursdays. Is there a way that I can get the number of sundays, tuesdays and wednesdays from January 2009 to September 30 2009 I use excel 2007 Thanking you Afd |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
number of specific weekdays
WOH!!
Thanks a million. I am amazed at your diligence and desire to help.I tried Ashish',Barry's and Lars formulas and all returned the same answer which I am certain is right. Different ways to do the same thing I am deeply grateful and looking forward to your continued help. Afd "Ashish Mathur" wrote in message ... Hi, Try this. C6 has 1/1/2009 and C7 has 30/9/2009 =SUMPRODUCT(1*(WEEKDAY(ROW(INDIRECT(C6&":"&C7)))={ 1,3,4})) -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "afdmello" wrote in message ... I undertake trainings on sundays, wednesdays and Thursdays. Is there a way that I can get the number of sundays, tuesdays and wednesdays from January 2009 to September 30 2009 I use excel 2007 Thanking you Afd |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
number of specific weekdays
On Sun, 18 Oct 2009 15:22:12 GMT, Lars-Åke Aspelin
wrote: On Sun, 18 Oct 2009 17:23:14 +0300, "afdmello" wrote: I undertake trainings on sundays, wednesdays and Thursdays. Is there a way that I can get the number of sundays, tuesdays and wednesdays from January 2009 to September 30 2009 I use excel 2007 Thanking you Afd If your (inclusive) start date is in cell A1 and your (includive) end date is in cell A2, try the following formula: =SUMPRODUCT(LOOKUP(WEEKDAY(A$1+ROW(OFFSET(A$1,,,A $2-A$1+1))),ROW($1:$7),{1,0,1,0,1,0,0})) The {1,0,1,0,1,0,0} at the end of the formula is a vector representing Sunday, Monday, ..., Saturday where I have put a 1 for those weekdays to be counted and a 0 for the others. Hope this helps / Lars-Åke Just noticed that there is an error in the formula I proposed, changed A$1 to A$7 in one place. Here is a the new version: =SUMPRODUCT(LOOKUP(WEEKDAY(A$1+ROW(OFFSET(A$7,,,A$ 2-A$1+1))),ROW($1:$7),{1,0,0,0,1,0,0})) Lars-Åke |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
number of specific weekdays
You are welcome
-- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "afdmello" wrote in message ... WOH!! Thanks a million. I am amazed at your diligence and desire to help.I tried Ashish',Barry's and Lars formulas and all returned the same answer which I am certain is right. Different ways to do the same thing I am deeply grateful and looking forward to your continued help. Afd "Ashish Mathur" wrote in message ... Hi, Try this. C6 has 1/1/2009 and C7 has 30/9/2009 =SUMPRODUCT(1*(WEEKDAY(ROW(INDIRECT(C6&":"&C7)))={ 1,3,4})) -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "afdmello" wrote in message ... I undertake trainings on sundays, wednesdays and Thursdays. Is there a way that I can get the number of sundays, tuesdays and wednesdays from January 2009 to September 30 2009 I use excel 2007 Thanking you Afd |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Count the Number of Weekdays by Month | Excel Discussion (Misc queries) | |||
Extract Number of Weekdays (Mon-Fri) & Saturdays for a particular Month/Yr | Excel Worksheet Functions | |||
Linking a Specific Word to a Specific Number | Excel Worksheet Functions | |||
Number of Weekdays | Excel Worksheet Functions | |||
How do i count number of weekdays between two dates? | Excel Worksheet Functions |