Thanks Dilip, I updated the formula and now it works PERFECT!!!!!
Date: Fri, 29 Jan 2010 00:37:57 +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, 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! -- ---------------------------------------------------------------------------------- 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. Recibe gratis un resumen diario de tu Hotmail en tu móvil. ¡Recíbelo ya! ¡Nuevo MSN Deportes! Sigue los partidos en directo y encuentra la última información de tus equipos favoritos. El NUEVO Internet Explorer 8 es el navegador más rápido y seguro ¡Descárgalo gratis! _________________________________________________________________ ¡Nuevo MSN Noticias! Vive la información las 24 horas, con un análisis profundo de la actualidad que más te interesa. http://noticias.es.msn.com/ -- ---------------------------------------------------------------------------------- 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
