Find Number Of Mondays In given Period

[kamlakar]

hi everybody
find Number Of Mondays In Period

I need the number of Monday's that occur within an interval between two dates,
I use the following Array Formula:

=SUM(IF(WEEKDAY(A2-1+ROW(INDIRECT("1:"&TRUNC(B2-A2)+1)))=C2,1,0))

This formula assumes the following:
A2 contains the beginning date of the interval
B2 contains the ending date of the interval
C2 contains the day-of-week number (1=Sunday)

but same one is not working in my spreadsheet.

can anybody guide me.?

 

[Russ At Index]

Hi Kamlakar,

Would pushing your data through a pivot table give
you the results you reqiure ???.

So if 2 = Monday , use "count of 2" within your table.

Hope this helps

Russ.

 

[kamlakar]

hi !

this is not pivot table data

even if i changed formula as per your suggestion
it wont worked.

please help me !

kamlakar

 

[Andrew Poulsom]

What you posted is an array formula. After typing it you must press Ctrl+Shift+Enter, not just Enter. If correctly entered Excel will surround it with curly braces {}.

 

[Richie(UK)]

Hi k,

Have you entered the array formula with the required CSE keystrokes?

CSE = Control+Shift+Enter

Chip's formula works OK when entered in that way.

 

[simsy]

Maybe I'm missing somthing here???

But I don't think you need an array formula...

I'm also not clear about what is in C2, i.e. why it's relevant to the problem if it's always Mondays you're interested in?

Do you want to include the start and end dates, or just the dates between?

I haven't fully formulated this yet, but what I'm thinking is along the lines of;

=INT((B2-A2/)7) plus you might need to add 1 depending on the start day and the size of the remainder...

There's always a Monday in every 7 day period right?

Unless, as I say, I'm missing something??

Regards,

Simsy

 

[fairwinds]

Hi,

Try:


=INT((B2-A2+WEEKDAY(A2-2))/7)

 

[kamlakar]

Thanks everybody

u have help me to solve my problem

really mr excel is great.

lot of thanks again !

kamlakar