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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.