|
Calculate number of days in a column of dates
Hi,
I have a sheet with a column "Day" where the production date is introduced for each mouvement (5-1-04; 6-1-04, 6,2,04...) along the year (those days can be repeated according to the production). I need to calculate how many days of production there is by month and by year. Can somebody help me???? Tks. Barbara |
Hi Barbara,
A bit more detail please. Does a cell in that column contain many dates or just 1? If the former, give an example of how you would manually calculate the production days with say 3 dates in the cell, and how the dates are differentiated. If the latter, what signifies a start date and what an end date? Do start and end dates get included? -- HTH Bob Phillips "Barbara" wrote in message ... Hi, I have a sheet with a column "Day" where the production date is introduced for each mouvement (5-1-04; 6-1-04, 6,2,04...) along the year (those days can be repeated according to the production). I need to calculate how many days of production there is by month and by year. Can somebody help me???? Tks. Barbara |
Hi Bob, thaks for your reply. I' ll be more specific giving you part of my
sheet. 05-01-2004 Vigaria 05-01-2004 Vigaria 06-01-2004 Vigaria 06-01-2004 Vigaria 06-01-2004 Vigaria 06-01-2004 Vigaria 07-01-2004 Vigaria 07-01-2004 Vigaria 08-01-2004 Vigaria 08-01-2004 Vigaria 08-01-2004 Vigaria 08-01-2004 Vigaria 09-01-2004 Cabouca 09-01-2004 Vigaria 09-01-2004 Vigaria 12-01-2004 Cabouca Here, we have 6 days of production. Hope that you can help me. "Bob Phillips" wrote: Hi Barbara, A bit more detail please. Does a cell in that column contain many dates or just 1? If the former, give an example of how you would manually calculate the production days with say 3 dates in the cell, and how the dates are differentiated. If the latter, what signifies a start date and what an end date? Do start and end dates get included? -- HTH Bob Phillips "Barbara" wrote in message ... Hi, I have a sheet with a column "Day" where the production date is introduced for each mouvement (5-1-04; 6-1-04, 6,2,04...) along the year (those days can be repeated according to the production). I need to calculate how many days of production there is by month and by year. Can somebody help me???? Tks. Barbara |
Is this simply the highest date less the lowest? If so, then
=NETWORKDAYS(MIN(A1:A1000),MAX(A1:A1000)) NETWORKDAYS is part of the analysis toolpak add-in, so that must be installed. -- HTH Bob Phillips "Barbara" wrote in message ... Hi Bob, thaks for your reply. I' ll be more specific giving you part of my sheet. 05-01-2004 Vigaria 05-01-2004 Vigaria 06-01-2004 Vigaria 06-01-2004 Vigaria 06-01-2004 Vigaria 06-01-2004 Vigaria 07-01-2004 Vigaria 07-01-2004 Vigaria 08-01-2004 Vigaria 08-01-2004 Vigaria 08-01-2004 Vigaria 08-01-2004 Vigaria 09-01-2004 Cabouca 09-01-2004 Vigaria 09-01-2004 Vigaria 12-01-2004 Cabouca Here, we have 6 days of production. Hope that you can help me. "Bob Phillips" wrote: Hi Barbara, A bit more detail please. Does a cell in that column contain many dates or just 1? If the former, give an example of how you would manually calculate the production days with say 3 dates in the cell, and how the dates are differentiated. If the latter, what signifies a start date and what an end date? Do start and end dates get included? -- HTH Bob Phillips "Barbara" wrote in message ... Hi, I have a sheet with a column "Day" where the production date is introduced for each mouvement (5-1-04; 6-1-04, 6,2,04...) along the year (those days can be repeated according to the production). I need to calculate how many days of production there is by month and by year. Can somebody help me???? Tks. Barbara |
Hi again,
I still have a problem!!! in this list, with your formula, it returns 16 days. But the right answer is 14. Why? How can I have the right answer? 02-Dez 02-Dez 02-Dez 02-Dez 02-Dez 02-Dez 02-Dez 02-Dez 03-Dez 07-Dez 07-Dez 09-Dez 10-Dez 10-Dez 13-Dez 13-Dez 13-Dez 14-Dez 14-Dez 15-Dez 15-Dez 15-Dez 15-Dez 16-Dez 16-Dez 17-Dez 17-Dez 17-Dez 20-Dez 20-Dez 21-Dez 21-Dez 21-Dez 21-Dez 21-Dez 22-Dez 22-Dez 23-Dez 23-Dez "Bob Phillips" wrote: Is this simply the highest date less the lowest? If so, then =NETWORKDAYS(MIN(A1:A1000),MAX(A1:A1000)) NETWORKDAYS is part of the analysis toolpak add-in, so that must be installed. -- HTH Bob Phillips "Barbara" wrote in message ... Hi Bob, thaks for your reply. I' ll be more specific giving you part of my sheet. 05-01-2004 Vigaria 05-01-2004 Vigaria 06-01-2004 Vigaria 06-01-2004 Vigaria 06-01-2004 Vigaria 06-01-2004 Vigaria 07-01-2004 Vigaria 07-01-2004 Vigaria 08-01-2004 Vigaria 08-01-2004 Vigaria 08-01-2004 Vigaria 08-01-2004 Vigaria 09-01-2004 Cabouca 09-01-2004 Vigaria 09-01-2004 Vigaria 12-01-2004 Cabouca Here, we have 6 days of production. Hope that you can help me. "Bob Phillips" wrote: Hi Barbara, A bit more detail please. Does a cell in that column contain many dates or just 1? If the former, give an example of how you would manually calculate the production days with say 3 dates in the cell, and how the dates are differentiated. If the latter, what signifies a start date and what an end date? Do start and end dates get included? -- HTH Bob Phillips "Barbara" wrote in message ... Hi, I have a sheet with a column "Day" where the production date is introduced for each mouvement (5-1-04; 6-1-04, 6,2,04...) along the year (those days can be repeated according to the production). I need to calculate how many days of production there is by month and by year. Can somebody help me???? Tks. Barbara |
Barbara,
Is this because NETWORKDAYS is including both the start and end dates as part of the difference, and you just want the days between? If so, I was going to say just subtract 2 from it, but just in case the max or Min dates are weekend days then use =NETWORKDAYS(MIN(A1:A1000),MAX(A1:A1000))-(WEEKDAY(MIN(A1:A1000),2)<6)-(WEEK DAY(MAX(A1:A1000),2)<6) -- HTH Bob Phillips "Barbara" wrote in message ... Hi again, I still have a problem!!! in this list, with your formula, it returns 16 days. But the right answer is 14. Why? How can I have the right answer? 02-Dez 02-Dez 02-Dez 02-Dez 02-Dez 02-Dez 02-Dez 02-Dez 03-Dez 07-Dez 07-Dez 09-Dez 10-Dez 10-Dez 13-Dez 13-Dez 13-Dez 14-Dez 14-Dez 15-Dez 15-Dez 15-Dez 15-Dez 16-Dez 16-Dez 17-Dez 17-Dez 17-Dez 20-Dez 20-Dez 21-Dez 21-Dez 21-Dez 21-Dez 21-Dez 22-Dez 22-Dez 23-Dez 23-Dez "Bob Phillips" wrote: Is this simply the highest date less the lowest? If so, then =NETWORKDAYS(MIN(A1:A1000),MAX(A1:A1000)) NETWORKDAYS is part of the analysis toolpak add-in, so that must be installed. -- HTH Bob Phillips "Barbara" wrote in message ... Hi Bob, thaks for your reply. I' ll be more specific giving you part of my sheet. 05-01-2004 Vigaria 05-01-2004 Vigaria 06-01-2004 Vigaria 06-01-2004 Vigaria 06-01-2004 Vigaria 06-01-2004 Vigaria 07-01-2004 Vigaria 07-01-2004 Vigaria 08-01-2004 Vigaria 08-01-2004 Vigaria 08-01-2004 Vigaria 08-01-2004 Vigaria 09-01-2004 Cabouca 09-01-2004 Vigaria 09-01-2004 Vigaria 12-01-2004 Cabouca Here, we have 6 days of production. Hope that you can help me. "Bob Phillips" wrote: Hi Barbara, A bit more detail please. Does a cell in that column contain many dates or just 1? If the former, give an example of how you would manually calculate the production days with say 3 dates in the cell, and how the dates are differentiated. If the latter, what signifies a start date and what an end date? Do start and end dates get included? -- HTH Bob Phillips "Barbara" wrote in message ... Hi, I have a sheet with a column "Day" where the production date is introduced for each mouvement (5-1-04; 6-1-04, 6,2,04...) along the year (those days can be repeated according to the production). I need to calculate how many days of production there is by month and by year. Can somebody help me???? Tks. Barbara |
Bob,
The problem here is because there are no records for 6-dec and 8-dec which are week days. Is there any other way to calculate the number of days introduced in my sheet? "Bob Phillips" wrote: Barbara, Is this because NETWORKDAYS is including both the start and end dates as part of the difference, and you just want the days between? If so, I was going to say just subtract 2 from it, but just in case the max or Min dates are weekend days then use =NETWORKDAYS(MIN(A1:A1000),MAX(A1:A1000))-(WEEKDAY(MIN(A1:A1000),2)<6)-(WEEK DAY(MAX(A1:A1000),2)<6) -- HTH Bob Phillips "Barbara" wrote in message ... Hi again, I still have a problem!!! in this list, with your formula, it returns 16 days. But the right answer is 14. Why? How can I have the right answer? 02-Dez 02-Dez 02-Dez 02-Dez 02-Dez 02-Dez 02-Dez 02-Dez 03-Dez 07-Dez 07-Dez 09-Dez 10-Dez 10-Dez 13-Dez 13-Dez 13-Dez 14-Dez 14-Dez 15-Dez 15-Dez 15-Dez 15-Dez 16-Dez 16-Dez 17-Dez 17-Dez 17-Dez 20-Dez 20-Dez 21-Dez 21-Dez 21-Dez 21-Dez 21-Dez 22-Dez 22-Dez 23-Dez 23-Dez "Bob Phillips" wrote: Is this simply the highest date less the lowest? If so, then =NETWORKDAYS(MIN(A1:A1000),MAX(A1:A1000)) NETWORKDAYS is part of the analysis toolpak add-in, so that must be installed. -- HTH Bob Phillips "Barbara" wrote in message ... Hi Bob, thaks for your reply. I' ll be more specific giving you part of my sheet. 05-01-2004 Vigaria 05-01-2004 Vigaria 06-01-2004 Vigaria 06-01-2004 Vigaria 06-01-2004 Vigaria 06-01-2004 Vigaria 07-01-2004 Vigaria 07-01-2004 Vigaria 08-01-2004 Vigaria 08-01-2004 Vigaria 08-01-2004 Vigaria 08-01-2004 Vigaria 09-01-2004 Cabouca 09-01-2004 Vigaria 09-01-2004 Vigaria 12-01-2004 Cabouca Here, we have 6 days of production. Hope that you can help me. "Bob Phillips" wrote: Hi Barbara, A bit more detail please. Does a cell in that column contain many dates or just 1? If the former, give an example of how you would manually calculate the production days with say 3 dates in the cell, and how the dates are differentiated. If the latter, what signifies a start date and what an end date? Do start and end dates get included? -- HTH Bob Phillips "Barbara" wrote in message ... Hi, I have a sheet with a column "Day" where the production date is introduced for each mouvement (5-1-04; 6-1-04, 6,2,04...) along the year (those days can be repeated according to the production). I need to calculate how many days of production there is by month and by year. Can somebody help me???? Tks. Barbara |
another thing which can happen is if there are days with no production which
is not a holiday or week end, the network days will give a high answer something which might work is =SUMPRODUCT(--(A1:A1000<A2:A1001),--(A1:A1000<"")) if all of the dates are in order. "Bob Phillips" wrote: Barbara, Is this because NETWORKDAYS is including both the start and end dates as part of the difference, and you just want the days between? If so, I was going to say just subtract 2 from it, but just in case the max or Min dates are weekend days then use =NETWORKDAYS(MIN(A1:A1000),MAX(A1:A1000))-(WEEKDAY(MIN(A1:A1000),2)<6)-(WEEK DAY(MAX(A1:A1000),2)<6) -- HTH Bob Phillips "Barbara" wrote in message ... Hi again, I still have a problem!!! in this list, with your formula, it returns 16 days. But the right answer is 14. Why? How can I have the right answer? 02-Dez 02-Dez 02-Dez 02-Dez 02-Dez 02-Dez 02-Dez 02-Dez 03-Dez 07-Dez 07-Dez 09-Dez 10-Dez 10-Dez 13-Dez 13-Dez 13-Dez 14-Dez 14-Dez 15-Dez 15-Dez 15-Dez 15-Dez 16-Dez 16-Dez 17-Dez 17-Dez 17-Dez 20-Dez 20-Dez 21-Dez 21-Dez 21-Dez 21-Dez 21-Dez 22-Dez 22-Dez 23-Dez 23-Dez "Bob Phillips" wrote: Is this simply the highest date less the lowest? If so, then =NETWORKDAYS(MIN(A1:A1000),MAX(A1:A1000)) NETWORKDAYS is part of the analysis toolpak add-in, so that must be installed. -- HTH Bob Phillips "Barbara" wrote in message ... Hi Bob, thaks for your reply. I' ll be more specific giving you part of my sheet. 05-01-2004 Vigaria 05-01-2004 Vigaria 06-01-2004 Vigaria 06-01-2004 Vigaria 06-01-2004 Vigaria 06-01-2004 Vigaria 07-01-2004 Vigaria 07-01-2004 Vigaria 08-01-2004 Vigaria 08-01-2004 Vigaria 08-01-2004 Vigaria 08-01-2004 Vigaria 09-01-2004 Cabouca 09-01-2004 Vigaria 09-01-2004 Vigaria 12-01-2004 Cabouca Here, we have 6 days of production. Hope that you can help me. "Bob Phillips" wrote: Hi Barbara, A bit more detail please. Does a cell in that column contain many dates or just 1? If the former, give an example of how you would manually calculate the production days with say 3 dates in the cell, and how the dates are differentiated. If the latter, what signifies a start date and what an end date? Do start and end dates get included? -- HTH Bob Phillips "Barbara" wrote in message ... Hi, I have a sheet with a column "Day" where the production date is introduced for each mouvement (5-1-04; 6-1-04, 6,2,04...) along the year (those days can be repeated according to the production). I need to calculate how many days of production there is by month and by year. Can somebody help me???? Tks. Barbara |
Hi again Barbara,
So you just want to count the unique dates, including Sat and Sun? =SUMPRODUCT((A1:A1000<"")/COUNTIF(A1:A1000,A1:A1000&"")) -- HTH Bob Phillips "Barbara" wrote in message ... Bob, The problem here is because there are no records for 6-dec and 8-dec which are week days. Is there any other way to calculate the number of days introduced in my sheet? "Bob Phillips" wrote: Barbara, Is this because NETWORKDAYS is including both the start and end dates as part of the difference, and you just want the days between? If so, I was going to say just subtract 2 from it, but just in case the max or Min dates are weekend days then use =NETWORKDAYS(MIN(A1:A1000),MAX(A1:A1000))-(WEEKDAY(MIN(A1:A1000),2)<6)-(WEEK DAY(MAX(A1:A1000),2)<6) -- HTH Bob Phillips "Barbara" wrote in message ... Hi again, I still have a problem!!! in this list, with your formula, it returns 16 days. But the right answer is 14. Why? How can I have the right answer? 02-Dez 02-Dez 02-Dez 02-Dez 02-Dez 02-Dez 02-Dez 02-Dez 03-Dez 07-Dez 07-Dez 09-Dez 10-Dez 10-Dez 13-Dez 13-Dez 13-Dez 14-Dez 14-Dez 15-Dez 15-Dez 15-Dez 15-Dez 16-Dez 16-Dez 17-Dez 17-Dez 17-Dez 20-Dez 20-Dez 21-Dez 21-Dez 21-Dez 21-Dez 21-Dez 22-Dez 22-Dez 23-Dez 23-Dez "Bob Phillips" wrote: Is this simply the highest date less the lowest? If so, then =NETWORKDAYS(MIN(A1:A1000),MAX(A1:A1000)) NETWORKDAYS is part of the analysis toolpak add-in, so that must be installed. -- HTH Bob Phillips "Barbara" wrote in message ... Hi Bob, thaks for your reply. I' ll be more specific giving you part of my sheet. 05-01-2004 Vigaria 05-01-2004 Vigaria 06-01-2004 Vigaria 06-01-2004 Vigaria 06-01-2004 Vigaria 06-01-2004 Vigaria 07-01-2004 Vigaria 07-01-2004 Vigaria 08-01-2004 Vigaria 08-01-2004 Vigaria 08-01-2004 Vigaria 08-01-2004 Vigaria 09-01-2004 Cabouca 09-01-2004 Vigaria 09-01-2004 Vigaria 12-01-2004 Cabouca Here, we have 6 days of production. Hope that you can help me. "Bob Phillips" wrote: Hi Barbara, A bit more detail please. Does a cell in that column contain many dates or just 1? If the former, give an example of how you would manually calculate the production days with say 3 dates in the cell, and how the dates are differentiated. If the latter, what signifies a start date and what an end date? Do start and end dates get included? -- HTH Bob Phillips "Barbara" wrote in message ... Hi, I have a sheet with a column "Day" where the production date is introduced for each mouvement (5-1-04; 6-1-04, 6,2,04...) along the year (those days can be repeated according to the production). I need to calculate how many days of production there is by month and by year. Can somebody help me???? Tks. Barbara |
| All times are GMT +1. The time now is 04:36 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com