Count of Weeks

[dhayes1963]

Can anyone advise how I could make a cell display the number of Mondays that have occurred during a specified date period ? I specifically want my worksheet to display a count of weeks (Mondays) between (and including) 01-Jul-2002 and (including) the current date. Thanks.

 

[PaddyD]

try this:

=INT((TODAY()-"01/07/2002")/7)+IF(WEEKDAY("01/07/2002"+3)+MOD(TODAY()-"01/07/2002",7)>=7,1)


paddy

 

[dhayes1963]

Thanks PADDYD. That did the trick.

 

[Yogi Anand]

Hi dhayes1963:

Try the following ...

'=INT((B2-A2)/7)+OR(AND(WEEKDAY(A2)<=C2,WEEKDAY(B2)>=C2),AND(WEEKDAY(B2)>=C2,WEEKDAY(A2)<=C2))*1


where A2 houses the starting date, B2 houses the ending date, and C2 houses the weekday number -- in your case 2 for Monday

Regards!
Yogi Anand

 

[Legacy 11779]

Since it is known that 1 Jul 2002 is a monday, the formula could be reduced to :-

=INT((TODAY()-"1/7/2002")/7)+1

This counts the number of Mondays.

However, if you just want the number of complete weeks starting from 1 Jul 2002 :-

=INT((TODAY()-"30/6/2002")/7)
This message was edited by Pear on 2002-11-02 09:40

 

[mneff]

Re: Count of # of Partial Weeks

I would like to count the number of weeks...including partial weeks? Does such a formula exist?

 

[barry houdini]

Hello mneff,

Welcome to MrExcel

You'd be more likely to get some helpful answers by starting a new thread (some users disregard threads with several answers), and you might want to give a few more details of what you want to do.

I'll have a guess....

If you have two dates, one in A2 and a later one in B2 then to find the number of weeks between them, rounded up so that a partial week is counted as a full one

=INT((B2-A2+6)/7)

or if you want to get a result like "4 weeks 3 days" try

=INT((B2-A2)/7)&" days "&MOD(B2-A2,7)&" days"