Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Days Count Sheet Help
I'm hoping someone can help. I'm working on a worksheet (Rollup!) that gives
me a count of days that a person is present in a given month from information pulled from another sheet (Data!). Data! includes Name (Column C), StartDate (A), EndDate (N), and FSource (L). Rollup! needs to include columns: Name, BeginDate, EndDate, FSource, and DaysCount. But it's funky. For starters, there will be two dropdowns on Data!, one for year (A1), one for month (B1) used to filter the records pulled from Data! for the specified year and month. For BeginDate I need it to be either the first day of the month or the StartDate depending on when the person started. For example if my focus month is December, 2009, and one person's BeginDate is 11/20/09, then the BeginDate next to their name needs to be 12/01/09. I'm trying to count the days the person is present in only that month. For EndDate, same thing. If the person ended within the month (EndDate=12/24/09), then I need that date, otherwise, if the person is still there through the last day of the month, I need the last day of the month inserted. So if this person was still there at the end of December, EndDate would be 12/31/09. Also, if there is no EndDate on Data!, then the last day of the selected month should be inserted. For DaysCount, I need it to simply count the days between BeginDate and EndDate with one exception. If the EndDate occurs within the month of focus (ie EndDate= 12/15/09 for December), DaysCount needs to be -1 day (because the last day doesn't count). For FSource I simply need to pull over the cooresponding FSource from Data!. Can anyone help with a formula for this? Thanks SO MUCH in advance. I know it's complicated but I'm sure it's possible. I unfortunately lack the Excel coding skill to get it done! --Dax -- I would give my left hand to be ambidextrous! |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Days Count Sheet Help
Hi,
So what do you need help with - determining start date or end date -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "Dax Arroway" wrote in message ... I'm hoping someone can help. I'm working on a worksheet (Rollup!) that gives me a count of days that a person is present in a given month from information pulled from another sheet (Data!). Data! includes Name (Column C), StartDate (A), EndDate (N), and FSource (L). Rollup! needs to include columns: Name, BeginDate, EndDate, FSource, and DaysCount. But it's funky. For starters, there will be two dropdowns on Data!, one for year (A1), one for month (B1) used to filter the records pulled from Data! for the specified year and month. For BeginDate I need it to be either the first day of the month or the StartDate depending on when the person started. For example if my focus month is December, 2009, and one person's BeginDate is 11/20/09, then the BeginDate next to their name needs to be 12/01/09. I'm trying to count the days the person is present in only that month. For EndDate, same thing. If the person ended within the month (EndDate=12/24/09), then I need that date, otherwise, if the person is still there through the last day of the month, I need the last day of the month inserted. So if this person was still there at the end of December, EndDate would be 12/31/09. Also, if there is no EndDate on Data!, then the last day of the selected month should be inserted. For DaysCount, I need it to simply count the days between BeginDate and EndDate with one exception. If the EndDate occurs within the month of focus (ie EndDate= 12/15/09 for December), DaysCount needs to be -1 day (because the last day doesn't count). For FSource I simply need to pull over the cooresponding FSource from Data!. Can anyone help with a formula for this? Thanks SO MUCH in advance. I know it's complicated but I'm sure it's possible. I unfortunately lack the Excel coding skill to get it done! --Dax -- I would give my left hand to be ambidextrous! |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Days Count Sheet Help
So if the Data sheet is:
Name StartDate EndDate FSource John Smith 11/25/09 12/20/09 Blue Fred Jones 11/26/09 01/15/10 Green Bill Blast 12/15/09 01/10/10 Blue And I selected a range of 12/2009 on the RollUp sheet. The result on the RollUp sheet would be: Name BeginDate EndDate FSource DaysCount John Smith 12/01/09 12/20/09 Blue 19 Fred Jones 12/01/09 12/31/09 Green 31 Bill Blast 12/15/09 12/31/09 Blue 16 Please let me know if there's other questions. --Dax -- I would give my left hand to be ambidextrous! "Ashish Mathur" wrote: Hi, So what do you need help with - determining start date or end date -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "Dax Arroway" wrote in message ... I'm hoping someone can help. I'm working on a worksheet (Rollup!) that gives me a count of days that a person is present in a given month from information pulled from another sheet (Data!). Data! includes Name (Column C), StartDate (A), EndDate (N), and FSource (L). Rollup! needs to include columns: Name, BeginDate, EndDate, FSource, and DaysCount. But it's funky. For starters, there will be two dropdowns on Data!, one for year (A1), one for month (B1) used to filter the records pulled from Data! for the specified year and month. For BeginDate I need it to be either the first day of the month or the StartDate depending on when the person started. For example if my focus month is December, 2009, and one person's BeginDate is 11/20/09, then the BeginDate next to their name needs to be 12/01/09. I'm trying to count the days the person is present in only that month. For EndDate, same thing. If the person ended within the month (EndDate=12/24/09), then I need that date, otherwise, if the person is still there through the last day of the month, I need the last day of the month inserted. So if this person was still there at the end of December, EndDate would be 12/31/09. Also, if there is no EndDate on Data!, then the last day of the selected month should be inserted. For DaysCount, I need it to simply count the days between BeginDate and EndDate with one exception. If the EndDate occurs within the month of focus (ie EndDate= 12/15/09 for December), DaysCount needs to be -1 day (because the last day doesn't count). For FSource I simply need to pull over the cooresponding FSource from Data!. Can anyone help with a formula for this? Thanks SO MUCH in advance. I know it's complicated but I'm sure it's possible. I unfortunately lack the Excel coding skill to get it done! --Dax -- I would give my left hand to be ambidextrous! |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Days Count Sheet Help
Hi,
Assume that the order of the names on both sheets is the same (assumed from your illustration below). Also, enter 12/2009 and 12/1/2009 I.e. 1 December 2009 in cell F2 of the Rollup sheet You may use this to determine the BeginDate. Copy down till where required =if(Data!$B2<F2,F2,Data!$B2) To determine the end date, use the following: =if(and(Data!$B2F2,Data!$B2<eomonth(F2,0)),Data!$ B2,eomonth(F2,0)) Since the order of names remains the same, the FSource can simply be copied and pasted I am not clear about the days count - please clarify -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "Dax Arroway" wrote in message ... So if the Data sheet is: Name StartDate EndDate FSource John Smith 11/25/09 12/20/09 Blue Fred Jones 11/26/09 01/15/10 Green Bill Blast 12/15/09 01/10/10 Blue And I selected a range of 12/2009 on the RollUp sheet. The result on the RollUp sheet would be: Name BeginDate EndDate FSource DaysCount John Smith 12/01/09 12/20/09 Blue 19 Fred Jones 12/01/09 12/31/09 Green 31 Bill Blast 12/15/09 12/31/09 Blue 16 Please let me know if there's other questions. --Dax -- I would give my left hand to be ambidextrous! "Ashish Mathur" wrote: Hi, So what do you need help with - determining start date or end date -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "Dax Arroway" wrote in message ... I'm hoping someone can help. I'm working on a worksheet (Rollup!) that gives me a count of days that a person is present in a given month from information pulled from another sheet (Data!). Data! includes Name (Column C), StartDate (A), EndDate (N), and FSource (L). Rollup! needs to include columns: Name, BeginDate, EndDate, FSource, and DaysCount. But it's funky. For starters, there will be two dropdowns on Data!, one for year (A1), one for month (B1) used to filter the records pulled from Data! for the specified year and month. For BeginDate I need it to be either the first day of the month or the StartDate depending on when the person started. For example if my focus month is December, 2009, and one person's BeginDate is 11/20/09, then the BeginDate next to their name needs to be 12/01/09. I'm trying to count the days the person is present in only that month. For EndDate, same thing. If the person ended within the month (EndDate=12/24/09), then I need that date, otherwise, if the person is still there through the last day of the month, I need the last day of the month inserted. So if this person was still there at the end of December, EndDate would be 12/31/09. Also, if there is no EndDate on Data!, then the last day of the selected month should be inserted. For DaysCount, I need it to simply count the days between BeginDate and EndDate with one exception. If the EndDate occurs within the month of focus (ie EndDate= 12/15/09 for December), DaysCount needs to be -1 day (because the last day doesn't count). For FSource I simply need to pull over the cooresponding FSource from Data!. Can anyone help with a formula for this? Thanks SO MUCH in advance. I know it's complicated but I'm sure it's possible. I unfortunately lack the Excel coding skill to get it done! --Dax -- I would give my left hand to be ambidextrous! |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Days Count Sheet Help
There's actually over 1500 entries which span over 3 years.
RollUp!A1 contains a dropdown of years: 2006 through 2020. RollUp!B1 contains a dropdown of months: 1 through 12. The list I gave was only partial. There are many and the names don't necessarily stay in the same order. There of course are those who would not show up. For example if I filtered for December, folks who have an EndDate of 11/29/09 or earlier would be filtered out. The DaysCount is simply the count of days between the BeginDate and the EndDate that results in the sheet. But something that's extra is that if the EndDate happens within the month of focus, the formula needs to subtract one day from the total. OK, so in Enlish the formula for Cell A2:however many names there are would be: Look at cell A1 and get the year, then look at cell B1 and get the month. Now look through the names of the Data sheet and select all of them whose begin dates are before the last day of the selected month. If they have an end date before the first day of the selected month, disregard. Insert the names of those listed down the column. In cell B2: For each name, if the begin date is within the selected month, insert that date, if the begin date is before the selected month, insert the first day of the selected month into cell. In cell C2: For each name, if the end date is not there, or is after the selected month, but the last day of the selected month. If the end date is within the selected month, insert that date. In Cell D2: For each name, enter their FSource. In Cell E2: Count the days between Cell B2 through C2 but if the date of C2 is withing the selected month, subtract one day. So, there are people coming and going all the time. Some are present for months. What we need to do is have a way of being able to take a snapshot of specific months, know who is present during that month, and how many days those specific people were present during that month. Sometimes it could be the whole month but sometimes it would only be a part of that month. They are however consecutive, obviously. Does that help explain it a bit better? --Dax -- I would give my left hand to be ambidextrous! "Ashish Mathur" wrote: Hi, Assume that the order of the names on both sheets is the same (assumed from your illustration below). Also, enter 12/2009 and 12/1/2009 I.e. 1 December 2009 in cell F2 of the Rollup sheet You may use this to determine the BeginDate. Copy down till where required =if(Data!$B2<F2,F2,Data!$B2) To determine the end date, use the following: =if(and(Data!$B2F2,Data!$B2<eomonth(F2,0)),Data!$ B2,eomonth(F2,0)) Since the order of names remains the same, the FSource can simply be copied and pasted I am not clear about the days count - please clarify -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "Dax Arroway" wrote in message ... So if the Data sheet is: Name StartDate EndDate FSource John Smith 11/25/09 12/20/09 Blue Fred Jones 11/26/09 01/15/10 Green Bill Blast 12/15/09 01/10/10 Blue And I selected a range of 12/2009 on the RollUp sheet. The result on the RollUp sheet would be: Name BeginDate EndDate FSource DaysCount John Smith 12/01/09 12/20/09 Blue 19 Fred Jones 12/01/09 12/31/09 Green 31 Bill Blast 12/15/09 12/31/09 Blue 16 Please let me know if there's other questions. --Dax -- I would give my left hand to be ambidextrous! "Ashish Mathur" wrote: Hi, So what do you need help with - determining start date or end date -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "Dax Arroway" wrote in message ... I'm hoping someone can help. I'm working on a worksheet (Rollup!) that gives me a count of days that a person is present in a given month from information pulled from another sheet (Data!). Data! includes Name (Column C), StartDate (A), EndDate (N), and FSource (L). Rollup! needs to include columns: Name, BeginDate, EndDate, FSource, and DaysCount. But it's funky. For starters, there will be two dropdowns on Data!, one for year (A1), one for month (B1) used to filter the records pulled from Data! for the specified year and month. For BeginDate I need it to be either the first day of the month or the StartDate depending on when the person started. For example if my focus month is December, 2009, and one person's BeginDate is 11/20/09, then the BeginDate next to their name needs to be 12/01/09. I'm trying to count the days the person is present in only that month. For EndDate, same thing. If the person ended within the month (EndDate=12/24/09), then I need that date, otherwise, if the person is still there through the last day of the month, I need the last day of the month inserted. So if this person was still there at the end of December, EndDate would be 12/31/09. Also, if there is no EndDate on Data!, then the last day of the selected month should be inserted. For DaysCount, I need it to simply count the days between BeginDate and EndDate with one exception. If the EndDate occurs within the month of focus (ie EndDate= 12/15/09 for December), DaysCount needs to be -1 day (because the last day doesn't count). For FSource I simply need to pull over the cooresponding FSource from Data!. Can anyone help with a formula for this? Thanks SO MUCH in advance. I know it's complicated but I'm sure it's possible. I unfortunately lack the Excel coding skill to get it done! --Dax -- I would give my left hand to be ambidextrous! |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Days Count Sheet Help
Any other takers on this??? PLEASE HELP! I'm so stuck!
-- I would give my left hand to be ambidextrous! "Dax Arroway" wrote: There's actually over 1500 entries which span over 3 years. RollUp!A1 contains a dropdown of years: 2006 through 2020. RollUp!B1 contains a dropdown of months: 1 through 12. The list I gave was only partial. There are many and the names don't necessarily stay in the same order. There of course are those who would not show up. For example if I filtered for December, folks who have an EndDate of 11/29/09 or earlier would be filtered out. The DaysCount is simply the count of days between the BeginDate and the EndDate that results in the sheet. But something that's extra is that if the EndDate happens within the month of focus, the formula needs to subtract one day from the total. OK, so in Enlish the formula for Cell A2:however many names there are would be: Look at cell A1 and get the year, then look at cell B1 and get the month. Now look through the names of the Data sheet and select all of them whose begin dates are before the last day of the selected month. If they have an end date before the first day of the selected month, disregard. Insert the names of those listed down the column. In cell B2: For each name, if the begin date is within the selected month, insert that date, if the begin date is before the selected month, insert the first day of the selected month into cell. In cell C2: For each name, if the end date is not there, or is after the selected month, but the last day of the selected month. If the end date is within the selected month, insert that date. In Cell D2: For each name, enter their FSource. In Cell E2: Count the days between Cell B2 through C2 but if the date of C2 is withing the selected month, subtract one day. So, there are people coming and going all the time. Some are present for months. What we need to do is have a way of being able to take a snapshot of specific months, know who is present during that month, and how many days those specific people were present during that month. Sometimes it could be the whole month but sometimes it would only be a part of that month. They are however consecutive, obviously. Does that help explain it a bit better? --Dax -- I would give my left hand to be ambidextrous! "Ashish Mathur" wrote: Hi, Assume that the order of the names on both sheets is the same (assumed from your illustration below). Also, enter 12/2009 and 12/1/2009 I.e. 1 December 2009 in cell F2 of the Rollup sheet You may use this to determine the BeginDate. Copy down till where required =if(Data!$B2<F2,F2,Data!$B2) To determine the end date, use the following: =if(and(Data!$B2F2,Data!$B2<eomonth(F2,0)),Data!$ B2,eomonth(F2,0)) Since the order of names remains the same, the FSource can simply be copied and pasted I am not clear about the days count - please clarify -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "Dax Arroway" wrote in message ... So if the Data sheet is: Name StartDate EndDate FSource John Smith 11/25/09 12/20/09 Blue Fred Jones 11/26/09 01/15/10 Green Bill Blast 12/15/09 01/10/10 Blue And I selected a range of 12/2009 on the RollUp sheet. The result on the RollUp sheet would be: Name BeginDate EndDate FSource DaysCount John Smith 12/01/09 12/20/09 Blue 19 Fred Jones 12/01/09 12/31/09 Green 31 Bill Blast 12/15/09 12/31/09 Blue 16 Please let me know if there's other questions. --Dax -- I would give my left hand to be ambidextrous! "Ashish Mathur" wrote: Hi, So what do you need help with - determining start date or end date -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "Dax Arroway" wrote in message ... I'm hoping someone can help. I'm working on a worksheet (Rollup!) that gives me a count of days that a person is present in a given month from information pulled from another sheet (Data!). Data! includes Name (Column C), StartDate (A), EndDate (N), and FSource (L). Rollup! needs to include columns: Name, BeginDate, EndDate, FSource, and DaysCount. But it's funky. For starters, there will be two dropdowns on Data!, one for year (A1), one for month (B1) used to filter the records pulled from Data! for the specified year and month. For BeginDate I need it to be either the first day of the month or the StartDate depending on when the person started. For example if my focus month is December, 2009, and one person's BeginDate is 11/20/09, then the BeginDate next to their name needs to be 12/01/09. I'm trying to count the days the person is present in only that month. For EndDate, same thing. If the person ended within the month (EndDate=12/24/09), then I need that date, otherwise, if the person is still there through the last day of the month, I need the last day of the month inserted. So if this person was still there at the end of December, EndDate would be 12/31/09. Also, if there is no EndDate on Data!, then the last day of the selected month should be inserted. For DaysCount, I need it to simply count the days between BeginDate and EndDate with one exception. If the EndDate occurs within the month of focus (ie EndDate= 12/15/09 for December), DaysCount needs to be -1 day (because the last day doesn't count). For FSource I simply need to pull over the cooresponding FSource from Data!. Can anyone help with a formula for this? Thanks SO MUCH in advance. I know it's complicated but I'm sure it's possible. I unfortunately lack the Excel coding skill to get it done! --Dax -- I would give my left hand to be ambidextrous! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Count Days | Excel Discussion (Misc queries) | |||
Count Days more than 180 days prior to today | Excel Worksheet Functions | |||
When word says Install I want it count the days for 3 days w.o wee | Excel Worksheet Functions | |||
COUNT how many ROWS ago out of 10 days that the highest high in 10 days was made | Excel Worksheet Functions | |||
Count # days | Excel Worksheet Functions |