Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
count weekday()=1
pls give me a single formula to count the desired number of specific weekday.
something like this =countif(start_date:end_date,weekday(1)) e.g. if start_date is 01/Dec/06 and end_date is 31/Dec/06, then find how many sundays are within this 2 dates. (in this case the count = 5) I hope someone out there can share their bright formulated gifts this new year. .. wish it is easy... dribler2. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
count weekday()=1
Try this:
With A1: (the start date) B1: (the end date) This formula counts the number of Sundays within that range =INT((B1-A1)/7)+OR(WEEKDAY(A1)=1,WEEKDAY(B1)=1) Note: 1=Sunday, 2=Monday.....7=Saturday Does that help? *********** Regards, Ron XL2002, WinXP "dribler2" wrote: pls give me a single formula to count the desired number of specific weekday. something like this =countif(start_date:end_date,weekday(1)) e.g. if start_date is 01/Dec/06 and end_date is 31/Dec/06, then find how many sundays are within this 2 dates. (in this case the count = 5) I hope someone out there can share their bright formulated gifts this new year. . wish it is easy... dribler2. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
count weekday()=1
thanks for your quick reply, Sir.
i just wish that my other post can have a straight from the heart golden solution. happy holidays dribler2 "Ron Coderre" wrote: Try this: With A1: (the start date) B1: (the end date) This formula counts the number of Sundays within that range =INT((B1-A1)/7)+OR(WEEKDAY(A1)=1,WEEKDAY(B1)=1) Note: 1=Sunday, 2=Monday.....7=Saturday Does that help? *********** Regards, Ron XL2002, WinXP "dribler2" wrote: pls give me a single formula to count the desired number of specific weekday. something like this =countif(start_date:end_date,weekday(1)) e.g. if start_date is 01/Dec/06 and end_date is 31/Dec/06, then find how many sundays are within this 2 dates. (in this case the count = 5) I hope someone out there can share their bright formulated gifts this new year. . wish it is easy... dribler2. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
count weekday()=1
One way:
A1 = start date A2 = end date =SUM(INT((WEEKDAY(A1-n,2)+A2-A1)/7)) Where n = day of week: Monday = 1 through Sunday = 7 Biff "dribler2" wrote in message ... pls give me a single formula to count the desired number of specific weekday. something like this =countif(start_date:end_date,weekday(1)) e.g. if start_date is 01/Dec/06 and end_date is 31/Dec/06, then find how many sundays are within this 2 dates. (in this case the count = 5) I hope someone out there can share their bright formulated gifts this new year. . wish it is easy... dribler2. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
count weekday()=1
That doesn't work as a "generic" formula.
A1 = 12/1/2006 B1 = 12/7/2006 Count of Sundays returns 0. Biff "Ron Coderre" wrote in message ... Try this: With A1: (the start date) B1: (the end date) This formula counts the number of Sundays within that range =INT((B1-A1)/7)+OR(WEEKDAY(A1)=1,WEEKDAY(B1)=1) Note: 1=Sunday, 2=Monday.....7=Saturday Does that help? *********** Regards, Ron XL2002, WinXP "dribler2" wrote: pls give me a single formula to count the desired number of specific weekday. something like this =countif(start_date:end_date,weekday(1)) e.g. if start_date is 01/Dec/06 and end_date is 31/Dec/06, then find how many sundays are within this 2 dates. (in this case the count = 5) I hope someone out there can share their bright formulated gifts this new year. . wish it is easy... dribler2. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
count weekday()=1
Nice catch, Biff
Warning: Lame excuse to follow... I had that formula in my junk pile, source unknown. It's been replaced with the one you posted...with credit to you (unless I hear differently) *********** Best Regards, Ron XL2002, WinXP "T. Valko" wrote: That doesn't work as a "generic" formula. A1 = 12/1/2006 B1 = 12/7/2006 Count of Sundays returns 0. Biff "Ron Coderre" wrote in message ... Try this: With A1: (the start date) B1: (the end date) This formula counts the number of Sundays within that range =INT((B1-A1)/7)+OR(WEEKDAY(A1)=1,WEEKDAY(B1)=1) Note: 1=Sunday, 2=Monday.....7=Saturday Does that help? *********** Regards, Ron XL2002, WinXP "dribler2" wrote: pls give me a single formula to count the desired number of specific weekday. something like this =countif(start_date:end_date,weekday(1)) e.g. if start_date is 01/Dec/06 and end_date is 31/Dec/06, then find how many sundays are within this 2 dates. (in this case the count = 5) I hope someone out there can share their bright formulated gifts this new year. . wish it is easy... dribler2. |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
count weekday()=1
Happy New Year Biff, Sir
Thanks for passing by. I thought you're on vacation.. I know u can help a lot of novice like me. Specially this coming New Year 2007. happy holidays romelsb "T. Valko" wrote: One way: A1 = start date A2 = end date =SUM(INT((WEEKDAY(A1-n,2)+A2-A1)/7)) Where n = day of week: Monday = 1 through Sunday = 7 Biff "dribler2" wrote in message ... pls give me a single formula to count the desired number of specific weekday. something like this =countif(start_date:end_date,weekday(1)) e.g. if start_date is 01/Dec/06 and end_date is 31/Dec/06, then find how many sundays are within this 2 dates. (in this case the count = 5) I hope someone out there can share their bright formulated gifts this new year. . wish it is easy... dribler2. |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
count weekday()=1
excuse me Sir Ron,
I got a minor problem between the formula you gave me and Biff's one, here is the dates start_date: 01 Jan 2007 End_date: 21 Aug 2012 My boss will surely dump me with all of his hair loosing questions. you formula gave me a count of 294 sundays Biff's one gave me a count of 295 sundays I dont know now which one should i reconcile to consider..i hope i will not count the colors he need plus the real sunday's count. I may not spend a a real holiday this new year. Please help. thank you sir, romelsb Thanks a lot "Ron Coderre" wrote: Try this: With A1: (the start date) B1: (the end date) This formula counts the number of Sundays within that range =INT((B1-A1)/7)+OR(WEEKDAY(A1)=1,WEEKDAY(B1)=1) Note: 1=Sunday, 2=Monday.....7=Saturday Does that help? *********** Regards, Ron XL2002, WinXP "dribler2" wrote: pls give me a single formula to count the desired number of specific weekday. something like this =countif(start_date:end_date,weekday(1)) e.g. if start_date is 01/Dec/06 and end_date is 31/Dec/06, then find how many sundays are within this 2 dates. (in this case the count = 5) I hope someone out there can share their bright formulated gifts this new year. . wish it is easy... dribler2. |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
count weekday()=1
unless I hear differently
Not mine! Not sure of the original source. Maybe daddylonglegs or Daniel M.? Those guys know their date formulas! Normally, I would've posted: =SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(A1&":"&B1)))=7)) It's the "long way" around but seems more intuitive to me. Biff "Ron Coderre" wrote in message ... Nice catch, Biff Warning: Lame excuse to follow... I had that formula in my junk pile, source unknown. It's been replaced with the one you posted...with credit to you (unless I hear differently) *********** Best Regards, Ron XL2002, WinXP "T. Valko" wrote: That doesn't work as a "generic" formula. A1 = 12/1/2006 B1 = 12/7/2006 Count of Sundays returns 0. Biff "Ron Coderre" wrote in message ... Try this: With A1: (the start date) B1: (the end date) This formula counts the number of Sundays within that range =INT((B1-A1)/7)+OR(WEEKDAY(A1)=1,WEEKDAY(B1)=1) Note: 1=Sunday, 2=Monday.....7=Saturday Does that help? *********** Regards, Ron XL2002, WinXP "dribler2" wrote: pls give me a single formula to count the desired number of specific weekday. something like this =countif(start_date:end_date,weekday(1)) e.g. if start_date is 01/Dec/06 and end_date is 31/Dec/06, then find how many sundays are within this 2 dates. (in this case the count = 5) I hope someone out there can share their bright formulated gifts this new year. . wish it is easy... dribler2. |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
count weekday()=1
You're welcome! I missed a couple of days due to a bad modem. I'm here
every day barring technical difficulties! Biff "dribler2" wrote in message ... Happy New Year Biff, Sir Thanks for passing by. I thought you're on vacation.. I know u can help a lot of novice like me. Specially this coming New Year 2007. happy holidays romelsb "T. Valko" wrote: One way: A1 = start date A2 = end date =SUM(INT((WEEKDAY(A1-n,2)+A2-A1)/7)) Where n = day of week: Monday = 1 through Sunday = 7 Biff "dribler2" wrote in message ... pls give me a single formula to count the desired number of specific weekday. something like this =countif(start_date:end_date,weekday(1)) e.g. if start_date is 01/Dec/06 and end_date is 31/Dec/06, then find how many sundays are within this 2 dates. (in this case the count = 5) I hope someone out there can share their bright formulated gifts this new year. . wish it is easy... dribler2. |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
count weekday()=1
I get 294 Sundays with both formulas, which is the correct answer. Check the
cell values you're referencing. After you resolve that issue, use the formula Biff posted...it's less complicated. *********** Regards, Ron XL2002, WinXP "dribler2" wrote: excuse me Sir Ron, I got a minor problem between the formula you gave me and Biff's one, here is the dates start_date: 01 Jan 2007 End_date: 21 Aug 2012 My boss will surely dump me with all of his hair loosing questions. you formula gave me a count of 294 sundays Biff's one gave me a count of 295 sundays I dont know now which one should i reconcile to consider..i hope i will not count the colors he need plus the real sunday's count. I may not spend a a real holiday this new year. Please help. thank you sir, romelsb Thanks a lot "Ron Coderre" wrote: Try this: With A1: (the start date) B1: (the end date) This formula counts the number of Sundays within that range =INT((B1-A1)/7)+OR(WEEKDAY(A1)=1,WEEKDAY(B1)=1) Note: 1=Sunday, 2=Monday.....7=Saturday Does that help? *********** Regards, Ron XL2002, WinXP "dribler2" wrote: pls give me a single formula to count the desired number of specific weekday. something like this =countif(start_date:end_date,weekday(1)) e.g. if start_date is 01/Dec/06 and end_date is 31/Dec/06, then find how many sundays are within this 2 dates. (in this case the count = 5) I hope someone out there can share their bright formulated gifts this new year. . wish it is easy... dribler2. |
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
count weekday()=1
Typo:
=SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(A1&":"&B1)))=7)) Should be: =SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(A1&":"&B1)),2)=7)) Biff "T. Valko" wrote in message ... unless I hear differently Not mine! Not sure of the original source. Maybe daddylonglegs or Daniel M.? Those guys know their date formulas! Normally, I would've posted: =SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(A1&":"&B1)))=7)) It's the "long way" around but seems more intuitive to me. Biff "Ron Coderre" wrote in message ... Nice catch, Biff Warning: Lame excuse to follow... I had that formula in my junk pile, source unknown. It's been replaced with the one you posted...with credit to you (unless I hear differently) *********** Best Regards, Ron XL2002, WinXP "T. Valko" wrote: That doesn't work as a "generic" formula. A1 = 12/1/2006 B1 = 12/7/2006 Count of Sundays returns 0. Biff "Ron Coderre" wrote in message ... Try this: With A1: (the start date) B1: (the end date) This formula counts the number of Sundays within that range =INT((B1-A1)/7)+OR(WEEKDAY(A1)=1,WEEKDAY(B1)=1) Note: 1=Sunday, 2=Monday.....7=Saturday Does that help? *********** Regards, Ron XL2002, WinXP "dribler2" wrote: pls give me a single formula to count the desired number of specific weekday. something like this =countif(start_date:end_date,weekday(1)) e.g. if start_date is 01/Dec/06 and end_date is 31/Dec/06, then find how many sundays are within this 2 dates. (in this case the count = 5) I hope someone out there can share their bright formulated gifts this new year. . wish it is easy... dribler2. |
#13
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
count weekday()=1
thanks to both of your golden heart Sir Ron and Sir Biff,
I'll place the 2 answers to my boss in my safebasket, it seems the right formula is not yet traced from where it comes from.. Otherwise, I will count one-by-one with the calendar...Sirs, do you know a link to find a calendar where the total number of sundays thru saturdays are accounted in a yearly basis...I hope i dont need to start counting hair losses and the colors and the sundays. more power and happy holidays, with no heart feelings for 2007 romelsb "T. Valko" wrote: One way: A1 = start date A2 = end date =SUM(INT((WEEKDAY(A1-n,2)+A2-A1)/7)) Where n = day of week: Monday = 1 through Sunday = 7 Biff "dribler2" wrote in message ... pls give me a single formula to count the desired number of specific weekday. something like this =countif(start_date:end_date,weekday(1)) e.g. if start_date is 01/Dec/06 and end_date is 31/Dec/06, then find how many sundays are within this 2 dates. (in this case the count = 5) I hope someone out there can share their bright formulated gifts this new year. . wish it is easy... dribler2. |
#14
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
count weekday()=1
Thanks to both,
the info is correct, i use now 1st formula provided by T.Valko. more power and happy holidays driller "Ron Coderre" wrote: I get 294 Sundays with both formulas, which is the correct answer. Check the cell values you're referencing. After you resolve that issue, use the formula Biff posted...it's less complicated. *********** Regards, Ron XL2002, WinXP "dribler2" wrote: excuse me Sir Ron, I got a minor problem between the formula you gave me and Biff's one, here is the dates start_date: 01 Jan 2007 End_date: 21 Aug 2012 My boss will surely dump me with all of his hair loosing questions. you formula gave me a count of 294 sundays Biff's one gave me a count of 295 sundays I dont know now which one should i reconcile to consider..i hope i will not count the colors he need plus the real sunday's count. I may not spend a a real holiday this new year. Please help. thank you sir, romelsb Thanks a lot "Ron Coderre" wrote: Try this: With A1: (the start date) B1: (the end date) This formula counts the number of Sundays within that range =INT((B1-A1)/7)+OR(WEEKDAY(A1)=1,WEEKDAY(B1)=1) Note: 1=Sunday, 2=Monday.....7=Saturday Does that help? *********** Regards, Ron XL2002, WinXP "dribler2" wrote: pls give me a single formula to count the desired number of specific weekday. something like this =countif(start_date:end_date,weekday(1)) e.g. if start_date is 01/Dec/06 and end_date is 31/Dec/06, then find how many sundays are within this 2 dates. (in this case the count = 5) I hope someone out there can share their bright formulated gifts this new year. . wish it is easy... dribler2. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Need formula to count spinoffs | Excel Worksheet Functions | |||
Subtotals by count | Excel Discussion (Misc queries) | |||
Count consecutive dates only | Excel Discussion (Misc queries) | |||
Count Position of Filtered TEXT cells in a column | Excel Worksheet Functions | |||
Count Position of Filtered TEXT cells in a column | Excel Worksheet Functions |