Hello Krishna Thanks a lot to both you and Ajit for looking into my problem. I appreciate the help provided by both of you.
Thanks Ashish "Of what use is freedom if it does not include the freedom to make mistakes" -- Mahatma Gandhi On 6/29/09, MOKSH.......... <krishna.ange...@gmail.com> wrote: > > Mr. Ashish & Ajit, > > Pls refer to the below formula > > =day(date(year(A1),month(a1)+1,0)) > > e.g. A1 = Date > > It counts the number of days in a month > > pls find the attached file for the simple solution, pls tell me if this > resolves your problem ( IN YELLOW PORTION) > > with regards > > Moksh > --- Be Creative --- > > > 2009/6/28 Ajit Navre <aquas...@gmail.com> > >> Based on your initial post, that is the possible solution. I do not know >> if it is that complex or you missed out on something. Anyways, one >> clarification, the int() approach in my previous mail, still needs the > 30 >> check, else it won’t work if the days worked is less than 30. >> >> >> >> Regards >> >> >> >> Ajit >> >> *From:* excel-macros@googlegroups.com [mailto: >> excel-mac...@googlegroups.com] *On Behalf Of *Ashish Pradhan >> *Sent:* Sunday, June 28, 2009 2:41 PM >> *To:* excel-macros@googlegroups.com >> *Subject:* $$Excel-Macros$$ Re: Help with Attendance Tracker & Salary >> Calculation >> >> >> >> >> Hi Ajit >> >> Thanks for the prompt response. Let me try your solution and revert back. >> (I thought I was missing something, just wasnt sure if it was relatively >> easy or a bit complicated) >> >> >> Thanks once again. >> >> Ashish >> >> >> "Of what use is freedom if it does not include the freedom to make >> mistakes" -- Mahatma Gandhi >> >> On 6/28/09, *Ajit Navre* <aquas...@gmail.com> wrote: >> >> HI, >> >> >> >> Your post itself has the answer. >> >> >> >> You say that even-if the person has worked all the days in a month, salary >> Is paid for 30 days. That is you are assuming a month to be of 30 days. For >> months with >30 days in them, the calculation of yours (Monthly Salary * >> (No. of Days Present / 30)), yields salary even for the days that are > >> 30. So the excess that the calculation shows is the salary for the 31stday. >> Fix is, (Monthly >> Salary * (if(No. of Days Present > 30, 30, No. Of Days Present) / 30)). Other >> way to put it would be – (Monthly Salary * Int(No. Of Days Present / >> 30)). >> >> >> >> Regards >> >> >> >> Ajit >> >> >> >> *From:* excel-macros@googlegroups.com [mailto: >> excel-mac...@googlegroups.com] *On Behalf Of *Ashish Pradhan >> *Sent:* Sunday, June 28, 2009 12:46 AM >> *To:* excel-macros@googlegroups.com >> *Subject:* $$Excel-Macros$$ Help with Attendance Tracker & Salary >> Calculation >> >> >> >> Hello >> >> I need help with some calculations in the attached excel file. >> >> What I am trying to do is to calculate the salaries of personnel depending >> on the total number of days worked. My problem is with the sheets which >> carry data for Months which have 31 days. All Salary Calculations are done >> on 30 day basis irrespective of the number of days in a month. (If there are >> holidays, salary is still paid for 30 days and similarly if a month has 31 >> days or 28 days, Salary is paid for 30 days) >> >> The attached excel file contains two work sheets - Sheet "June" and Sheet >> "July" >> >> I will try to articulate my problem as: >> >> Sheet "July" ---- All calculations seem fine except for Column K, Column M >> and Column N (Highlighted in Red). Here, the persons have worked for all 31 >> days of the month. Now using the formula (Monthly Salary * (No. of Days >> Present / 30)), I get a figure which is more than the persons total salary. >> >> Is there any way to resolve this? >> >> >> Thanks in advance... >> >> -- >> >> >> >> >> > > > -- > Moksh > > > > > -- Ashish --~--~---------~--~----~------------~-------~--~----~ ------------------------------------------------------------------------------------- Some important links for excel users: 1. Excel and VBA Tutorials(Video and Text), Free add-ins downloads at http://www.excelitems.com 2. Excel tutorials at http://www.excel-macros.blogspot.com 3. Learn VBA Macros at http://www.vbamacros.blogspot.com 4. 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 or Ashish Jain @ 26may.1...@gmail.com ------------------------------------------------------------------------------------- -~----------~----~----~----~------~----~------~--~---
