interesting day calculation problem - not finding the solution

[jnaeve]

Hello,
I have an interesting day calculation problem that I'm really
struggling with and am trying to figure out how to present this.

First let me say that I've explored the numerous methods for WEEKDAY -
DATE - NETWORKDAYS - DATDIF and others but still can't seem to arrive
at the result I need. I may have to go to a VBA routine to calculate
this but wanted to post this for further input. By the way, I've
searched the forum and didn't see a resolution that may have been
already posted.

My challenge:
I have a date range represented in two different cells.

Start Date: E15
End Date: G15

I have check boxes selected to determine which days of the week will be
"work days". (we have unusual requests that require us to work odd
days and this form will calculate the occurance of any specific day)

Let's say for this example, my "Start Date:" = October 10, 2003
My "End Date:" = October 31, 2003

My check boxes tell me that I'm only going to work Monday, Wednesday,
Friday and Saturday. I need to determine, how many Monday's,
Wednesday's, Friday's and Saturday's are in that date range from "Start
Date" to "End Date".

My results from the formula should tell me that there are 3-Monday's,
3-Wednesday's, 4-Friday's and 4-Saturday's. I can't seem to get
there.

Any insight would be greatly appreciated!
Regards,
Jim Naeve

 

[Peo Sjoblom]

For total days

=SUMPRODUCT(0+(WEEKDAY(ROW(INDIRECT(A1&":"&A2)),2)={1,3,5,6}))

for Mondays

=SUMPRODUCT(0+(WEEKDAY(ROW(INDIRECT(A1&":"&A2)),2)=1))

Wednesdays

=SUMPRODUCT(0+(WEEKDAY(ROW(INDIRECT(A1&":"&A2)),2)=3))

change to 5 and 6 for Fridays and Saturdays

Btw, I get 4 Fridays and 3 for the rest

A1 holds start date and A2 end date

 

[jnaeve]

The right direction, thanks a bunch but I'm still not getting the right
breakdown. Using your formula for counting specific days, I'm not
getting 4 Friday's but instead just 3. However, what's interesting is
I get 4 Thursdays and that just doesn't compute, considering my start
date is 10/10/03 or Friday.

By the way, you were correct, I mis-counted the days (did that
manually).

Using your formula;
=SUMPRODUCT(0+(WEEKDAY(ROW(INDIRECT(A1&":"&A2)),2)=1)) changing the 1
for 2-7 depending on the day, I get the following result for the date
range 10/10/03 through 10/31/03:
3 - Sundays = correct
3 - Mondays = correct
3 - Tuesdays = correct
3 - Wednesdays = correct
4 - Thursdays = incorrect - should be 3
3 - Fridays = incorrect - should be 4
3 - Saturdays = correct

I can't quite figure this one out. I believe you're on the right
track, I just think I'm missing something here.

Any thoughts?

Regards,
Jim

 

[jnaeve]

I tried another date range for my "Start date" and "Stop Date" and got
the following results:

Start Date = 11/1/03
Stop Date = 11/24/03

4 - Sundays = Correct
3 - Mondays = Incorrect, should be 4
3 - Tuesdays = Correct
3 - Wednesdays = Correct
3 - Thursdays = Correct
4 - Fridays = Incorrect, should be 3
4 - Saturdays = Correct

In both instances, the total count of active days within the range is
correct but the placement for the count is not correct. Unusual, can't
quite understand where the anomoly is.

Thoughts, ideas?

Thanks again.
Jim

 

[Peo Sjoblom]

It works, for Thursdays

=SUMPRODUCT(0+(WEEKDAY(ROW(INDIRECT(A1&":"&A2)),2)=4))

returns 3 not 4, which is correct

=SUMPRODUCT(0+(WEEKDAY(ROW(INDIRECT(A1&":"&A2)),2)=5))

for Fridays returns 4 which is correct

 

[jnaeve]

WOW! This is truly bizarre!

I have two systems, one a Mac and the other a Windows 2000 - both get
the same result as I stated earlier. In one of my books by John
Walkenbach, (Excel 2002 Formulas) he makes a comment that some day/date
formulas may return different results from different regions.

I'm wondering if you're country code is different than mine (using
basically a US English format) - - - could this be the difference?

Not sure how that would affect it but would like to know your thoughts
on this. I've scrubbed the calculation twice on both machines and
still get the same result.

Weird!
Jim

 

[Peo Sjoblom]

It works

formula returns this

Mon 4
Tue 3
Wed 3
Thu 3
Fri 3
Sat 4
Sun 4

Given your other post I am curious how you count the days?

 

[jnaeve]

I think I just realized where the error is. Your formula:
=SUMPRODUCT(0+(WEEKDAY(ROW(INDIRECT(A1&":"&A2)),2)=1))

Makes the assumption that my date range cells are located consecutive;
i.e. A1 and A2. When in actuality, my cells are not consecutive but
are separated by one cell;
Start Date: E15
End Date: G15

Is it your understanding that this formula with the ROW statement would
potentially cause this error?

Let me know what you think.

Regards,
Jim

 

[Peo Sjoblom]

If you want I can send you a workbook with these dates and my formulas.

 

[jnaeve]

Please do Peo,
I apologize for dragging you down this rabbit trail but I'm really
stumped. I recreated another workbook myself and basically created the
raw formulas you suggested, I still get the same result.

Please forward to my email address:
(e-mail address removed)

Thanks, hopefully there will be something there to enlighten me.

I appreciate the time.
Jim