WeekDay count between dates

[Andy]

I have a number of records and each has a field of start and end dates. What
I need to work out is how I can count for each day of the week, how many days
are included in this date range (excluding the last date).

i.e.

Start: 01/04/2008
End: 12/04/2008.

Monday = 1
Tuesday = 2
Wednesday = 2
Thursday = 2
Friday = 2
Saturday = 1
Sunday = 1

 

[FSt1]

hi
look into the =networkday() formula. it's part of the analysis toolpac and
may not be readily available. check tools> Addins.... on the menu bar to see
if it is available. if not it should be on your excel/office install disc.

Regards
FSt1

 

[Andy]

Thanks but this would only give me a count of working days. I need a count of
each day of the week between the two days. A count for the number of Mondays,
count for the number of Tuesdays etc...

 

[Bernd P]

Hello,

If you do not need holidays then my date formula at
http://www.sulprobil.com/html/date_formulas.html
applies:

Array-enter
=INT((A2-MOD(A2-ROW(INDIRECT("2:8")),7)-A1+7)/7)
where A2 is your end date minus 1 (11/04/2008 here). Of course you can
also take your original date and exchange A2 by A2-1 in this formula.

Regards,
Bernd

 

[Andy]

sorry I'm either missing something or not explaining it properly. From what I
see of the below, this again will only give me a count of days between the
two dates, but not a count for each day of the week.

I am imagining that I will need 7 columns, one for each day of the week with
a slightly different forumla in to count the number of 'Monday' between the
two dates, another for the count of 'Tuesday' between the two dates etc.

 

[Bernd P]

Hello,

Yes, that's what I mean:

Select 7 adjacent vertical cells and array-enter my formula.

Should work...It did for me.

Regards,
Bernd

 

[FSt1]

hi
sorry, i misunderstood. in that case, see this site...
http://www.cpearson.com/excel/DateTimeWS.htm

scroll down to "number of mondays in a period"

Regards
FSt1

 

[T. Valko]

Try this:

A1 = start date
B1 = end date

A3:A9 = Monday, Tuesday, Wednesday, etc, etc

Enter this formula in B3 and copy down to B9:

=SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(A$1&":"&B$1-1)),2)=ROWS(B$3:B3)))

 

[Andy]

Hi there, thanks for the help this far. I need the days of the week in
columns however and cannot seem to get the formulae converted to suit. Would
you be able to provide the details to fit this structure?

http://f4.filecrunch.com/files/20080429/ca3ebdd180eed5a21730fcf00aa620bd/Book1.xls

 

[T. Valko]

Enter this formula in D2 and copy across to J2 then down as needed:

=SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT($A2&":"&$B2-1)))=COLUMNS($D22)))

 

[Andy]

Hi, thanks again for the help. I've tested the formula you've given but it
does not seemt to work for the third row of data:
http://f4.filecrunch.com/files/20080430/ee6f6e732f4f8184867e9599f144a7db/Book1.xls

Thursday and Friday should be '0'....

 

[T. Valko]

That link doesn't work.

What are the dates?

 

[Andy]

I have gotten this to work, thanks for everyone's input though!


http://www.excelforum.com/showthread.php?p=1914050&posted=1#post1914050

 

[Ron Rosenfeld]

I have a number of records and each has a field of start and end dates. What
I need to work out is how I can count for each day of the week, how many days
are included in this date range (excluding the last date).

i.e.

Start: 01/04/2008
End: 12/04/2008.

Monday = 1
Tuesday = 2
Wednesday = 2
Thursday = 2
Friday = 2
Saturday = 1
Sunday = 1

In general:

=INT((A2-WEEKDAY(A2+1-DOW)-A1+8)/7)

where DOW = 1 for Sunday; 2 for Monday; etc.

This formula assumes the starting date (A1) and ending date (A2) are both to be
considered. In your example, however, you are not counting the Last Date (you
only have one Saturday -- 05/04/2008; and you are not counting 12/04/2008). So
you will need to modify the formula slightly so as not to include that last
date, by subtracting one from each occurrence of A2:

=INT(($A$2-1-WEEKDAY($A$2-C1)-$A$1+8)/7)

--ron