| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Figuring number of Mon. Tues. Wed between dates
Course begins January 1, 2008
Course ends May 30, 2008 How many Tuesdays are there. Of course the dates will change. Thanks |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Figuring number of Mon. Tues. Wed between dates
=SUM(IF(WEEKDAY(ROW(INDIRECT("$1:$"&enddate-startdate+1))+$F$2-1)=3,1,0))
Change the =3 to 2 for Mondays and 4 for Wednesdays. This is an **array** formula. After typing the formula, instead of pressing just ENTER, press CTRL+SHIFT+ENTER. -- ** John C ** Please remember if your question is answered, to mark it answered :). It helps everyone. "Paula" wrote: Course begins January 1, 2008 Course ends May 30, 2008 How many Tuesdays are there. Of course the dates will change. Thanks |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Figuring number of Mon. Tues. Wed between dates
One way...
A1 = start date B1 = end date =SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(A1&":"&A151)),2)=2)) Where =2 means the 2nd day of the week = Tuesday -- Biff Microsoft Excel MVP "Paula" wrote in message ... Course begins January 1, 2008 Course ends May 30, 2008 How many Tuesdays are there. Of course the dates will change. Thanks |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Figuring number of Mon. Tues. Wed between dates
I've been on an array formula kick, but I like your formula, but you might
wanna change the A151 in the INDIRECT statement to B1 :) -- ** John C ** "T. Valko" wrote: One way... A1 = start date B1 = end date =SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(A1&":"&A151)),2)=2)) Where =2 means the 2nd day of the week = Tuesday -- Biff Microsoft Excel MVP "Paula" wrote in message ... Course begins January 1, 2008 Course ends May 30, 2008 How many Tuesdays are there. Of course the dates will change. Thanks |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Figuring number of Mon. Tues. Wed between dates
and of course, the =2 needs to be changed to =3, where 3=Tuesday :)
-- ** John C ** "T. Valko" wrote: One way... A1 = start date B1 = end date =SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(A1&":"&A151)),2)=2)) Where =2 means the 2nd day of the week = Tuesday -- Biff Microsoft Excel MVP "Paula" wrote in message ... Course begins January 1, 2008 Course ends May 30, 2008 How many Tuesdays are there. Of course the dates will change. Thanks |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Figuring number of Mon. Tues. Wed between dates
nevermind on this part :), but i stand by the A151 to B1 correction :)
-- ** John C ** "John C" wrote: and of course, the =2 needs to be changed to =3, where 3=Tuesday :) -- ** John C ** "T. Valko" wrote: One way... A1 = start date B1 = end date =SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(A1&":"&A151)),2)=2)) Where =2 means the 2nd day of the week = Tuesday -- Biff Microsoft Excel MVP "Paula" wrote in message ... Course begins January 1, 2008 Course ends May 30, 2008 How many Tuesdays are there. Of course the dates will change. Thanks |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Figuring number of Mon. Tues. Wed between dates
change the A151 in the INDIRECT statement to B1 :)
Ooops! Thanks for catching that! -- Biff Microsoft Excel MVP "John C" <johnc@stateofdenial wrote in message ... I've been on an array formula kick, but I like your formula, but you might wanna change the A151 in the INDIRECT statement to B1 :) -- ** John C ** "T. Valko" wrote: One way... A1 = start date B1 = end date =SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(A1&":"&A151)),2)=2)) Where =2 means the 2nd day of the week = Tuesday -- Biff Microsoft Excel MVP "Paula" wrote in message ... Course begins January 1, 2008 Course ends May 30, 2008 How many Tuesdays are there. Of course the dates will change. Thanks |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Figuring number of Mon. Tues. Wed between dates
Typo...
A1 = start date B1 = end date =SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(A1&":"&A151)),2)=2)) Formula should be: =SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(A1&":"&B1)),2)=2)) -- Biff Microsoft Excel MVP "T. Valko" wrote in message ... One way... A1 = start date B1 = end date =SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(A1&":"&A151)),2)=2)) Where =2 means the 2nd day of the week = Tuesday -- Biff Microsoft Excel MVP "Paula" wrote in message ... Course begins January 1, 2008 Course ends May 30, 2008 How many Tuesdays are there. Of course the dates will change. Thanks |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Figuring number of Mon. Tues. Wed between dates
Hi,
Here is another formula: =SUMPRODUCT(--(MOD(ROW(INDIRECT(G23&":"&G24)),7)=3)) -- Thanks, Shane Devenshire "Paula" wrote: Course begins January 1, 2008 Course ends May 30, 2008 How many Tuesdays are there. Of course the dates will change. Thanks |
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Figuring number of Mon. Tues. Wed between dates
Here's a much more compact formula that does the same thing:
=INT((WEEKDAY(A1-n,2)+B1-A1)/7) Where n = day of week: Monday =1 thru Sunday =7 I don't ever suggest it because I don't understand the logic behind it (and no one has been able to explain it to me!) and if someone asks for an explanation I won't have one! I don't post anything I can't explain. -- Biff Microsoft Excel MVP "John C" <johnc@stateofdenial wrote in message ... I've been on an array formula kick, but I like your formula, but you might wanna change the A151 in the INDIRECT statement to B1 :) -- ** John C ** "T. Valko" wrote: One way... A1 = start date B1 = end date =SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(A1&":"&A151)),2)=2)) Where =2 means the 2nd day of the week = Tuesday -- Biff Microsoft Excel MVP "Paula" wrote in message ... Course begins January 1, 2008 Course ends May 30, 2008 How many Tuesdays are there. Of course the dates will change. Thanks |
|
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Figuring number of Mon. Tues. Wed between dates
Thanks so much. I have tried both formulas and I am not doing something
correct. I have the beginning date in A1 and the end date in B1. I am not seeing where B1 is referenced. I changed A151 to B1 but it did not work. I actually do not understand the formula such as -- but of course that is OK if I can just get it to work. I am getting a REF error. This is the formula in A4. I have copied it =SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(A1&":"&A151)),2)=2)) Sorry for the confusion. "T. Valko" wrote: One way... A1 = start date B1 = end date =SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(A1&":"&A151)),2)=2)) Where =2 means the 2nd day of the week = Tuesday -- Biff Microsoft Excel MVP "Paula" wrote in message ... Course begins January 1, 2008 Course ends May 30, 2008 How many Tuesdays are there. Of course the dates will change. Thanks |
|
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Figuring number of Mon. Tues. Wed between dates
It worked I changed the A151 to A2 which is where I have the end date. Thanks
so much. "T. Valko" wrote: One way... A1 = start date B1 = end date =SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(A1&":"&A151)),2)=2)) Where =2 means the 2nd day of the week = Tuesday -- Biff Microsoft Excel MVP "Paula" wrote in message ... Course begins January 1, 2008 Course ends May 30, 2008 How many Tuesdays are there. Of course the dates will change. Thanks |
|
#13
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Figuring number of Mon. Tues. Wed between dates
Good deal. Sorry about the confusion caused by my typo.
Thanks for the feedback! -- Biff Microsoft Excel MVP "Paula" wrote in message ... It worked I changed the A151 to A2 which is where I have the end date. Thanks so much. "T. Valko" wrote: One way... A1 = start date B1 = end date =SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(A1&":"&A151)),2)=2)) Where =2 means the 2nd day of the week = Tuesday -- Biff Microsoft Excel MVP "Paula" wrote in message ... Course begins January 1, 2008 Course ends May 30, 2008 How many Tuesdays are there. Of course the dates will change. Thanks |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Figuring Age | Excel Worksheet Functions | |||
| I want to count the total Number of dates between two dates How? | Excel Worksheet Functions | |||
| Calculating number of days between two dates that fall between two other dates | Excel Discussion (Misc queries) | |||
| In excel how set up a sequence to drag Mon, Tues, Wed Etc, | Excel Discussion (Misc queries) | |||
| Need number of Saturdays and number of Sundays between 2 dates | Excel Worksheet Functions | |||
Linear Mode
