WeekDay count between dates

  • Thread starter Thread starter Andy
  • Start date Start date
A

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
 
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
 
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...
 
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
 
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.
 
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
 
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)))
 
Enter this formula in D2 and copy across to J2 then down as needed:

=SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT($A2&":"&$B2-1)))=COLUMNS($D2:D2)))
 
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
Click to expand...

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
 
Back
Top