Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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
|
|||
|
|||
=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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Number of days since a date | New Users to Excel | |||
Formula to count number of days in range which are less than today | Excel Worksheet Functions | |||
Calculating interest on number of days in the period | Excel Worksheet Functions | |||
Calculating for number of days when values are in dates | Excel Worksheet Functions | |||
Help! I am stuck calculating Days, Hours, Mins please help | Excel Worksheet Functions |