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

"Andy" wrote:

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

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

"FSt1" wrote:

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" wrote:

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

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.

"Bernd P" wrote:

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

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

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

"Andy" wrote:

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

"FSt1" wrote:

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" wrote:

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

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)))

--
Biff
Microsoft Excel MVP


"Andy" wrote in message
...
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 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/20080...20bd/Book1.xls

"T. Valko" wrote:

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)))

--
Biff
Microsoft Excel MVP


"Andy" wrote in message
...
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

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

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

--
Biff
Microsoft Excel MVP


"Andy" wrote in message
...
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/20080...20bd/Book1.xls

"T. Valko" wrote:

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)))

--
Biff
Microsoft Excel MVP


"Andy" wrote in message
...
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, 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/20080...a7db/Book1.xls

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

"T. Valko" wrote:

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

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

--
Biff
Microsoft Excel MVP


"Andy" wrote in message
...
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/20080...20bd/Book1.xls

"T. Valko" wrote:

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)))

--
Biff
Microsoft Excel MVP


"Andy" wrote in message
...
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

That link doesn't work.

What are the dates?

--
Biff
Microsoft Excel MVP


"Andy" wrote in message
...
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/20080...a7db/Book1.xls

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

"T. Valko" wrote:

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

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

--
Biff
Microsoft Excel MVP


"Andy" wrote in message
...
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/20080...20bd/Book1.xls

"T. Valko" wrote:

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)))

--
Biff
Microsoft Excel MVP


"Andy" wrote in message
...
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

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


http://www.excelforum.com/showthread...=1#post1914050


"T. Valko" wrote:

That link doesn't work.

What are the dates?

--
Biff
Microsoft Excel MVP


"Andy" wrote in message
...
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/20080...a7db/Book1.xls

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

"T. Valko" wrote:

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

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

--
Biff
Microsoft Excel MVP


"Andy" wrote in message
...
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/20080...20bd/Book1.xls

"T. Valko" wrote:

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)))

--
Biff
Microsoft Excel MVP


"Andy" wrote in message
...
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

On Fri, 25 Apr 2008 07:13:05 -0700, Andy
wrote:

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