calculating number of days (e.g., Mondays) between two dates

[Guest]

I need to count the number of days between a start date and end date and the
questions about this I found here don't seem to help (boggle me!) I need a
universal formula that will cover variable days which are listed in a
separate column:
G (rows) = names of days; J (rows) = start date; K (rows) = end date. On
top of this, I am sometimes having to count more than one day (up to three)
within a week (e.g., Mondays AND Fridays) which I'd be willing to put in
separate columns (i.e., columns G,H,I) if needed, but if only one column has
a day listed, the formula needs to ignore the empty columns.
A knotty problem?

 

[Bernie Deitrick]

=IF(MOD(TRUNC(K1-J1)+1,7)>MOD(MATCH(G1,{"Saturday","Sunday","Monday","Tuesday","Wednesday","Thursday","Friday"},FALSE)-1-INT(MOD(J1,7))+7,7),1,0)+INT((INT(K1-J1)+1)/7)+IF(MOD(TRUNC(K1-J1)+1,7)>MOD(MATCH(H1,{"Saturday","Sunday","Monday","Tuesday","Wednesday","Thursday","Friday"},FALSE)-1-INT(MOD(J1,7))+7,7),1,0)+INT((INT(K1-J1)+1)/7)+IF(MOD(TRUNC(K1-J1)+1,7)>MOD(MATCH(I1,{"Saturday","Sunday","Monday","Tuesday","Wednesday","Thursday","Friday"},FALSE)-1-INT(MOD(J1,7))+7,7),1,0)+INT((INT(K1-J1)+1)/7)

All in one cell - watch the line breaks. Written for row 1, but can be copied down. I'm sure
Harlan will come up with a much shorter formula - the array formula that I had that was one third
shorter didn't work with blanks, so I gave up on that approach.

HTH,
Bernie
MS Excel MVP

 

[Bernie Deitrick]

That one only worked if all three days were needed. Try this version instead:

=IF(G1<>"",IF(MOD(TRUNC(K1-J1)+1,7)>MOD(MATCH(G1,{"Saturday","Sunday","Monday","Tuesday","Wednesday","Thursday","Friday"},FALSE)-1-INT(MOD(J1,7))+7,7),1,0)+INT((INT(K1-J1)+1)/7))+IF(H1<>"",IF(MOD(TRUNC(K1-J1)+1,7)>MOD(MATCH(H1,{"Saturday","Sunday","Monday","Tuesday","Wednesday","Thursday","Friday"},FALSE)-1-INT(MOD(J1,7))+7,7),1,0)+INT((INT(K1-J1)+1)/7))+IF(I1<>"",IF(MOD(TRUNC(K1-J1)+1,7)>MOD(MATCH(I1,{"Saturday","Sunday","Monday","Tuesday","Wednesday","Thursday","Friday"},FALSE)-1-INT(MOD(J1,7))+7,7),1,0)+INT((INT(K1-J1)+1)/7))

 

[Biff]

Hi!

After seeing Bernie's post I'm wondering if I understand the question!

To count specific weekdays between 2 dates (inclusive):

Where Monday = weekday 1 and Sunday = weekday 7

Start date in A1
End date in B1

=SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(A1&":"&B1)),2)=weekday_number))

To count more than 1 weekday like Mondays and Tuesdays:

=SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(A1&":"&B1)),2)={1,2}))

Biff

 

[Bernie Deitrick]

Biff,

Neat formula! - converting the dates to row numbers was very insightful.

Bernie

 

[Guest]

This is great, but it doesn’t factor the variability of the days tried to
explain in my initial query. Data in columns G, H, and I will be days of
week, but will change from row to row (as will the respective dates in
columns N and O). Sometimes there will be no data in H and/or I. I am
hoping to create something where a clerk can put in the days of the week and
the spreadsheet formula will count up the days. Note: the number of days
will then be used in another formula to calculated $$.

Perhaps if I show you what I have so far will help:

G3 H3 I3 N3 O3 Q3
M F (blank) 10/9/05 12/15/05 (answer)

I tried Bernie’s formula but that didn’t work. I had to change cell
references, and I don’t understand the formula (and cannot decipher Excel
Help’s explanation) so that might be why.
I am currently using IF formulae to reference columns G, H and I into the
weekday_number, but think I have exceeded the quantity of formulae one can
put in a single cell.

If you have an answer, WHAT A RELIEF!!!

THX.

 

[Biff]

Hi!

Let's see if we understand you.....

Columns G, H and I, may or may not contain the letter abbreviations for the
days of the week.

G3 H3 I3 N3 O3 Q3
M F (blank) 10/9/05 12/15/05 (answer)

So, based on your example above, you want to count the Mondays and Fridays
between 10/9/2005 and 15/15/2005.

Is that what you want?

Try this.....

Just make sure that the abbreviations you use match what are in this formua:

=IF(COUNT(N3:O3)<>2,"",SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(N3&":"&O3)),2)=MATCH(G3:I3,{"M","T","W","TH","F","SA","SU",0},0))))

Biff

 

[Robert_Steel]

Biff
I like the use of the Row() function as you have used it in array
formulas.
However, and risking looking a bit picky...<g>
when used in dates we have an iminent Y2K situation on the 5 June 2079.
(the date value of the last row)

A small fix you could employ
SUMPRODUCT(--(WEEKDAY(A1-1+ROW(INDIRECT("1:"&B1-A1)),2)={1,2}))
or in your final formula

=IF(COUNT(N3:O3)<>2,"",SUMPRODUCT(--(WEEKDAY(N3-1+ROW(INDIRECT("1:"&O3-N3)),2)=MATCH(G3:I3,{"M","T","W","TH","F","SA","SU",0},0))))

just using the Row() to increment the date not be the date.

This is still not perfect as you can only have dates approx 179 years
apart.

hope it's of interest
RES

 

[Guest]

Brilliant!

I don't understand it, but it works! It looks like you maxed out the
formulae for a cell so I will simply have to teach a clerk to fill down on
the column when entering data.

Thanks for your help.

 

[Biff]

You're welcome! Thanks for the feedback.

Biff

Brilliant!

I don't understand it, but it works! It looks like you maxed out the
formulae for a cell so I will simply have to teach a clerk to fill down on
the column when entering data.

Thanks for your help.

 

[Biff]

Hi!

Actually, I've thought about the June 5 2079 row limitation many times but I
personally have never had to come up a formula that projects that far into
the future so I never bothered looking into a fix.

Maybe in one of the future versions of Excel MS will increase the number of
rows and solve that limitation for us!

Cheers

Biff