Thread: Find Number Of Mondays In given Period Thanks: 0 Likes: 0

1. Find Number Of Mondays In given Period

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.?

2. 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.

3. Find Number Of Mondays In given Period

hi !

this is not pivot table data

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

kamlakar

4. 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 {}.

5. 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.

6. 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

7. Hi,

Try:

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

8. Find Number Of Mondays In given Period

Thanks everybody

u have help me to solve my problem

really mr excel is great.

lot of thanks again !

kamlakar

User Tag List

Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•