Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
girlfriend in school
 
Posts: n/a
Default calculating number of days (e.g., Mondays) between two dates

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?
  #2   Report Post  
Bernie Deitrick
 
Posts: n/a
Default

=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


"girlfriend in school" wrote in message
...
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?



  #3   Report Post  
Bernie Deitrick
 
Posts: n/a
Default

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

--
HTH,
Bernie
MS Excel MVP


"Bernie Deitrick" <deitbe @ consumer dot org wrote in message
...
=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


"girlfriend in school" wrote in message
...
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?





  #4   Report Post  
Biff
 
Posts: n/a
Default

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

To count more than 1 weekday like Mondays and Tuesdays:

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

Biff

"girlfriend in school" wrote
in message ...
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?



  #5   Report Post  
Bernie Deitrick
 
Posts: n/a
Default


Biff,

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

Bernie

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





  #6   Report Post  
girlfriend in school
 
Posts: n/a
Default

This is great, but it doesnt 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 Bernies formula but that didnt work. I had to change cell
references, and I dont understand the formula (and cannot decipher Excel
Helps 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" wrote:

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

To count more than 1 weekday like Mondays and Tuesdays:

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

Biff

"girlfriend in school" wrote
in message ...
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?




  #7   Report Post  
Biff
 
Posts: n/a
Default

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

"girlfriend in school" wrote
in message ...
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" wrote:

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

To count more than 1 weekday like Mondays and Tuesdays:

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

Biff

"girlfriend in school"
wrote
in message ...
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?






  #8   Report Post  
 
Posts: n/a
Default

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
  #9   Report Post  
girlfriend in school
 
Posts: n/a
Default

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

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

"girlfriend in school" wrote
in message ...
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" wrote:

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

To count more than 1 weekday like Mondays and Tuesdays:

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

Biff

"girlfriend in school"
wrote
in message ...
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?






  #10   Report Post  
Biff
 
Posts: n/a
Default

You're welcome! Thanks for the feedback.

Biff

"girlfriend in school" wrote
in message ...
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" wrote:

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

"girlfriend in school"
wrote
in message ...
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" wrote:

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

To count more than 1 weekday like Mondays and Tuesdays:

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

Biff

"girlfriend in school"
wrote
in message ...
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?










  #11   Report Post  
Biff
 
Posts: n/a
Default

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

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



Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Number of days since a date Jim M New Users to Excel 2 June 28th 05 12:30 AM
Formula to count number of days in range which are less than today zooming Excel Worksheet Functions 2 June 21st 05 04:01 PM
Calculating interest on number of days in the period Ron Excel Worksheet Functions 0 January 18th 05 01:59 AM
Calculating for number of days when values are in dates pumper Excel Worksheet Functions 7 January 17th 05 05:52 AM
Help! I am stuck calculating Days, Hours, Mins please help OB1 Excel Worksheet Functions 2 November 15th 04 07:14 PM


All times are GMT +1. The time now is 11:53 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"