Dear Cecilia, If you want to include next year range within that formula set, then you might want to go ahead an include function YEAR with MONTH. For example:-
MONTH(T$2)=MONTH($C3) will change to :- AND(MONTH(T$2)=MONTH($C3),YEAR(T$2)=YEAR($C3)) Suggestion:- you should inlclude new year right after the previous year's data, means you should go from top to bottom rather than going towards left to right. Best Regards, -- DILIP KUMAR PANDEY MBA-HR,B COM(Hons.),BCA Mobile: +91 9810929744 dilipan...@gmail.com dilipan...@yahoo.com New Delhi - 110062 On 1/27/10, Cecilia Chiderski <cecic...@hotmail.com> wrote: > > Hi Dilip, please, needing your help to update the formula, if 2 years > calendar. See attached .xls (just the EOMONTH formula, I am OK with the > others). > Thanks! > Cecilia > > > > > ------------------------------ > Date: Sun, 24 Jan 2010 12:30:00 +0530 > > Subject: Re: $$Excel-Macros$$ Function to return days by month > From: dilipan...@gmail.com > To: cecic...@hotmail.com > CC: excel-macros@googlegroups.com > > Dear Cecilia, > > Both the tasks are accomplished. > 1) Formula is upgraded for upper section. > > 2) EOMONTH is no longer a problem. ATP will automatically be installed, if > the user has not installed them before. Workbook Open event code is now > included to take care of this. > > Note:- Conditional formatting is included in Upper section to hide the > errors, if any. Remove the same if you do not need. > Data Validation is included in Start Date and End Date, Remove the same if > you do not need. > > Best of luck..!! > > Let me know in case of any queries. Template still has many chances to > upgrade, but I followed the route which I took initially and reached to the > goal. :) > > Best Regards, > -- > DILIP KUMAR PANDEY > MBA-HR,B COM(Hons.),BCA > Mobile: +91 9810929744 > dilipan...@gmail.com > dilipan...@yahoo.com > New Delhi - 110062 > > > On 1/24/10, *Cecilia Chiderski* <cecic...@hotmail.com> wrote: > > Here we go.... sumproduct on By Resource. I added two dummy columns, but > doesn't hurt. Question: Why if January is not planned, in your formula by > month January still shows 1?? (try removing contents from C3:F3). > > If I can make EOMONTH available for any user either by installing ATP or > adding EOMONTH as UDF, would be perfect... > > Thanks, > Cecilia > > > > ------------------------------ > From: cecic...@hotmail.com > To: dilipan...@gmail.com > Subject: RE: $$Excel-Macros$$ Function to return days by month > Date: Sat, 23 Jan 2010 18:38:35 +0100 > > After sending the e-mail to you that question came to my mind...... how to > automatically enable ATP when opening the file?!?!?!?! :) > > I am working on sumproduct, will send you what I can find, I am sure that > can be done.... > > THANKS, > Cecilia > > > ------------------------------ > Date: Sat, 23 Jan 2010 23:04:52 +0530 > Subject: Re: $$Excel-Macros$$ Function to return days by month > From: dilipan...@gmail.com > To: cecic...@hotmail.com > CC: excel-macros@googlegroups.com > > Thanks Celcilia, > > There are other workarounds available, which we can use instead of > EOMONTH. and now I got your main requirement as well. > > If you wish, you can include a code which will autmatically enable / > installs the ATP, when the users open the workbook. > > For the later part, I was also looking for SUMPRODUCT, but let me consider > other options as well and would get back to you on this front. > > Best Regards, > -- > DILIP KUMAR PANDEY > MBA-HR,B COM(Hons.),BCA > Mobile: +91 9810929744 > dilipan...@gmail.com > dilipan...@yahoo.com > New Delhi - 110062 > > > On 1/23/10, *Cecilia Chiderski* <cecic...@hotmail.com> wrote: > > Dilip, thanks a lot, but my problem is EOMONTH function. Isn't part of the > Analisys Toolpack?? I can't use it, since this file will be distributed to > more than 2000 people, and they might or might not have the addin installed. > Is it possible to add the EOMONTH function as UDF, thus it can be available > for everybody?? > Also, on the table at the bottom (by resource), I would like to improve my > formulas, since in the real file I will have more than 1000 lines. I was > trying with sumproduct logic, but can't make it work (Div/0 error). Any > ideas? > Thanks a lot, > Cecilia > > > ------------------------------ > Date: Sat, 23 Jan 2010 20:13:22 +0530 > Subject: Re: $$Excel-Macros$$ Function to return days by month > From: dilipan...@gmail.com > To: excel-macros@googlegroups.com > CC: cecic...@hotmail.com > > > Dear Cecilia, > > Solved workbook is attached herewith. I foresee that the solution can > futher be customised, if you find this interesting and useful. > > Let me know in case of any queries. > > > Best Regards, > -- > DILIP KUMAR PANDEY > MBA-HR,B COM(Hons.),BCA > Mobile: +91 9810929744 > dilipan...@gmail.com > dilipan...@yahoo.com > New Delhi - 110062 > > > On 1/23/10, *Cecilia Chiderski* <cecic...@hotmail.com> wrote: > > I need help. I have a Start Date and End Date, formated as date. I need to > calculate the days by month covered by this start/end. Example is attached. > Also, based on the days by month, I need to calendarize assignment by > resource (see attached file). > I CAN NOT use in this model any of the Analysis Toolpack functions. I CAN > use VBA UDF. > Thanks, > Cecilia > > ------------------------------ > Llévate gratis un Mazda 2 y una Xbox 360. ¡Participa con > Hotmail!<http://www.vivelive.com/historiasdehotmail/> > > -- > > ---------------------------------------------------------------------------------- > Some important links for excel users: > 1. Follow us on TWITTER for tips tricks and links : > http://twitter.com/exceldailytip > 2. Excel and VBA Tutorials(Video and Text), Free add-ins downloads at > http://www.excelitems.com > 3. Excel tutorials at http://www.excel-macros.blogspot.com > 4. Learn VBA Macros at http://www.quickvba.blogspot.com > 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com > > > To post to this group, send email to excel-macros@googlegroups.com > If you find any spam message in the group, please send an email to: > Ayush Jain @ jainayus...@gmail.com > <><><><><><><><><><><><><><><><><><><><><><> > HELP US GROW !! > > We reach over 6,700 subscribers worldwide and receive many nice notes about > the learning and support from the group.Let friends and co-workers know they > can subscribe to group at > http://groups.google.com/group/excel-macros/subscribe > > > > > ------------------------------ > ¡Nuevo MSN Deportes! Sigue los partidos en directo y encuentra la última > información de tus equipos favoritos. <http://deportes.es.msn.com/> > > > > > ------------------------------ > Recibe gratis un resumen diario de tu Hotmail en tu móvil. ¡Recíbelo > ya!<http://serviciosmoviles.es.msn.com/hotmail/movistar-particulares.aspx> > ------------------------------ > ¡Nuevo MSN Deportes! Sigue los partidos en directo y encuentra la última > información de tus equipos favoritos. <http://deportes.es.msn.com/> > > > > ------------------------------ > El NUEVO Internet Explorer 8 es el navegador más rápido y seguro ¡Descárgalo > gratis! <http://www.vivelive.com/internetexplorer8/> > > -- ---------------------------------------------------------------------------------- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Excel and VBA Tutorials(Video and Text), Free add-ins downloads at http://www.excelitems.com 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com If you find any spam message in the group, please send an email to: Ayush Jain @ jainayus...@gmail.com <><><><><><><><><><><><><><><><><><><><><><> HELP US GROW !! We reach over 6,700 subscribers worldwide and receive many nice notes about the learning and support from the group.Let friends and co-workers know they can subscribe to group at http://groups.google.com/group/excel-macros/subscribe
