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 31st day. 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... -- --~--~---------~--~----~------------~-------~--~----~ ------------------------------------------------------------------------------------- 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 ------------------------------------------------------------------------------------- -~----------~----~----~----~------~----~------~--~---
