Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Payrol calculations
Hi all,
Difficult one for wizards! I am trying to compile a payroll for ships crew whose pay is calculated on a 30 day basis irrespective of calendar days in month. So for instance for the month of february 08, assuming a salary of 1,000, and the crew member signed on say 14 february his salary would be calculated on (29 days - 14 =15 days pay) 15/30 x 1,000 or 500 or if he joined say on 17 march 08 (31 days - 17 = 15 days pay) again his salary would be 15/30 x 1,000 or 500. Can someone assist in compiling a single formula which would work for any set of dates during the year/s, including overlapping months say 14 Feb 08 to 17 Mar 08. Many thanks/sgl |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Payrol calculations
Hi,
I'm confused by the logic of this. If somene starts on 14/2/2008 then they don't work 13 days of that month so do work 16 not 15 as in your example. In your second example 31-17=14 not 15 That aside try this =IF(DAY(A1)=1,B1,((EOMONTH(A1,0)-A1+1)/30)*B1) If you get the name error then load the analysis toolpak. I don't understand the second part of your question Can someone assist in compiling a single formula which would work for any set of dates during the year/s, including overlapping months say 14 Feb 08 to 17 Mar 08. Mike "sgl" wrote: Hi all, Difficult one for wizards! I am trying to compile a payroll for ships crew whose pay is calculated on a 30 day basis irrespective of calendar days in month. So for instance for the month of february 08, assuming a salary of 1,000, and the crew member signed on say 14 february his salary would be calculated on (29 days - 14 =15 days pay) 15/30 x 1,000 or 500 or if he joined say on 17 march 08 (31 days - 17 = 15 days pay) again his salary would be 15/30 x 1,000 or 500. Can someone assist in compiling a single formula which would work for any set of dates during the year/s, including overlapping months say 14 Feb 08 to 17 Mar 08. Many thanks/sgl |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Payrol calculations
forgot to mention, start date in a1, salary in b1
"Mike H" wrote: Hi, I'm confused by the logic of this. If somene starts on 14/2/2008 then they don't work 13 days of that month so do work 16 not 15 as in your example. In your second example 31-17=14 not 15 That aside try this =IF(DAY(A1)=1,B1,((EOMONTH(A1,0)-A1+1)/30)*B1) If you get the name error then load the analysis toolpak. I don't understand the second part of your question Can someone assist in compiling a single formula which would work for any set of dates during the year/s, including overlapping months say 14 Feb 08 to 17 Mar 08. Mike "sgl" wrote: Hi all, Difficult one for wizards! I am trying to compile a payroll for ships crew whose pay is calculated on a 30 day basis irrespective of calendar days in month. So for instance for the month of february 08, assuming a salary of 1,000, and the crew member signed on say 14 february his salary would be calculated on (29 days - 14 =15 days pay) 15/30 x 1,000 or 500 or if he joined say on 17 march 08 (31 days - 17 = 15 days pay) again his salary would be 15/30 x 1,000 or 500. Can someone assist in compiling a single formula which would work for any set of dates during the year/s, including overlapping months say 14 Feb 08 to 17 Mar 08. Many thanks/sgl |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Payrol calculations
"sgl" wrote:
I am trying to compile a payroll for ships crew whose pay is calculated on a 30 day basis irrespective of calendar days in month. You probably want to use the DAYS360() function. Be sure to read the Help page regarding US v. European assumptions. So for instance for the month of february 08, assuming a salary of 1,000, and the crew member signed on say 14 february his salary would be calculated on (29 days - 14 =15 days pay) 15/30 x 1,000 First, you have an off-by-one computation error. If a person starts on Feb 14 and works through Feb 29, they have worked 16 days: 29 - 14 + 1. (Proof: If they onlyl Feb 14, we would compute 14 - 14 + 1, not 14 - 14; that is, one day, not zero.) Second, if they do work through Feb 29 and you want to compute pay "on a 30 day basis irrespective of calendar days in month", it seems to me that they should receive 17/30 of a month's pay, not 16/30. (Proof: If they start on Feb 1 and work through Feb 29, they should receive a full month's pay -- 30/30 -- not 29/30.) Using DAYS360(), always enter the date __after__ the last day for "end date". For example, DAYS360(DATE(2008,2,14),DATE(2008,3,1)) computes the number of days between Feb 14 and Feb 29 inclusive, based on a 30-day month. Can someone assist in compiling a single formula which would work for any set of dates during the year/s, including overlapping months say 14 Feb 08 to 17 Mar 08. DAYS360(DATE(2008,2,14),DATE(2008,3,18)) Note that I use Mar 18 for "end date", not Mar 17. Also, the DATE() expressions can be replaced by cell references that reflect start and end date. For example: DAYS360(A1, B1+1) |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Payrol calculations
Errata....
I wrote: Second, if they do work through Feb 29 and you want to compute pay "on a 30 day basis irrespective of calendar days in month", it seems to me that they should receive 17/30 of a month's pay, not 16/30. (Proof: If they start on Feb 1 and work through Feb 29, they should receive a full month's pay -- 30/30 -- not 29/30.) On second thought, I don't agree with that. I believe the correct answer is 16/29 of a month's pay. So if the starting and ending dates (A1 and B1 respectively) are in the same month (of the same year), I believe the expression should be: pay * (B1 - A1 + 1) / day(eomonth(B1,0)) Sorry, I don't have time to develop a more general solution at the moment. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Payrol calculations
Thank you for your response. My arithmetic was completely off. My apologies.
Perhaps I did not phrase my question correctly so here goes again. Assuming pay is 1,000 per month StartDate EndDate 15 Feb 08 17 May 08 Pay is calculated for both days inclusive - 15 and 17 are calculated as pay days - in this example there is a complete calendar month of 31 days for March and therefore the pay is 1,000 - irrespective that the number of days is 31 - For February the crew member is entitled to 15 Days pay - 15 to 29 Feb 08 - 15 days/30 = 500.00 - irrespective that Feb has 29 days - For March the crew member is entitled to a full calendar month's pay of 1,000 irrespective that March has 31 days - For April the crew memebr is entitled to a full calendar month's pay of 1,000 - For May the crew member is entitled to 17 days pay/30 = 566.67 irrespective that May has 31 days In some instances the employment contract may be based on a calendar month basis - 1,000 per month irrespective of month days - which makes life a lot easier. My apologies for the confusion, I hope that I have made the problem clearer now. Thank you/sgl " wrote: "sgl" wrote: I am trying to compile a payroll for ships crew whose pay is calculated on a 30 day basis irrespective of calendar days in month. You probably want to use the DAYS360() function. Be sure to read the Help page regarding US v. European assumptions. So for instance for the month of february 08, assuming a salary of 1,000, and the crew member signed on say 14 february his salary would be calculated on (29 days - 14 =15 days pay) 15/30 x 1,000 First, you have an off-by-one computation error. If a person starts on Feb 14 and works through Feb 29, they have worked 16 days: 29 - 14 + 1. (Proof: If they onlyl Feb 14, we would compute 14 - 14 + 1, not 14 - 14; that is, one day, not zero.) Second, if they do work through Feb 29 and you want to compute pay "on a 30 day basis irrespective of calendar days in month", it seems to me that they should receive 17/30 of a month's pay, not 16/30. (Proof: If they start on Feb 1 and work through Feb 29, they should receive a full month's pay -- 30/30 -- not 29/30.) Using DAYS360(), always enter the date __after__ the last day for "end date". For example, DAYS360(DATE(2008,2,14),DATE(2008,3,1)) computes the number of days between Feb 14 and Feb 29 inclusive, based on a 30-day month. Can someone assist in compiling a single formula which would work for any set of dates during the year/s, including overlapping months say 14 Feb 08 to 17 Mar 08. DAYS360(DATE(2008,2,14),DATE(2008,3,18)) Note that I use Mar 18 for "end date", not Mar 17. Also, the DATE() expressions can be replaced by cell references that reflect start and end date. For example: DAYS360(A1, B1+1) |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Payrol calculations
On Mar 10, 8:19 am, sgl wrote:
My apologies. Perhaps I did not phrase my question correctly so here goes again Actually, I think your question was clear to begin with. I apologize for muddying the water with my second follow-up ("errata"). Your second example (below) makes it clear that the solution I have in mind will work better for you. Since I am not in position to test it right now, I am relunctant to post it. I hope to post it tonight unless someone beats me to it. PS: Are you sure that you are computing the partial-month wages according to the laws in your jurisdiction or contractual agreements? I believe it is not exactly how a solution using DAYS360() would do it; yet I thought DAYS360() exists specifically for this type of situation. No matter. It's a nitpick. I think the correct general solution can be adapted to either way. Just curious. ---- original message On Mar 10, 8:19 am, sgl wrote: Thank you for your response. My arithmetic was completely off. My apologies. Perhaps I did not phrase my question correctly so here goes again. Assuming pay is 1,000 per month StartDate EndDate 15 Feb 08 17 May 08 Pay is calculated for both days inclusive - 15 and 17 are calculated as pay days - in this example there is a complete calendar month of 31 days for March and therefore the pay is 1,000 - irrespective that the number of days is 31 - For February the crew member is entitled to 15 Days pay - 15 to 29 Feb 08 - 15 days/30 = 500.00 - irrespective that Feb has 29 days - For March the crew member is entitled to a full calendar month's pay of 1,000 irrespective that March has 31 days - For April the crew memebr is entitled to a full calendar month's pay of 1,000 - For May the crew member is entitled to 17 days pay/30 = 566.67 irrespective that May has 31 days In some instances the employment contract may be based on a calendar month basis - 1,000 per month irrespective of month days - which makes life a lot easier. My apologies for the confusion, I hope that I have made the problem clearer now. Thank you/sgl " wrote: "sgl" wrote: I am trying to compile a payroll for ships crew whose pay is calculated on a 30 day basis irrespective of calendar days in month. You probably want to use the DAYS360() function. Be sure to read the Help page regarding US v. European assumptions. So for instance for the month of february 08, assuming a salary of 1,000, and the crew member signed on say 14 february his salary would be calculated on (29 days - 14 =15 days pay) 15/30 x 1,000 First, you have an off-by-one computation error. If a person starts on Feb 14 and works through Feb 29, they have worked 16 days: 29 - 14 + 1. (Proof: If they onlyl Feb 14, we would compute 14 - 14 + 1, not 14 - 14; that is, one day, not zero.) Second, if they do work through Feb 29 and you want to compute pay "on a 30 day basis irrespective of calendar days in month", it seems to me that they should receive 17/30 of a month's pay, not 16/30. (Proof: If they start on Feb 1 and work through Feb 29, they should receive a full month's pay -- 30/30 -- not 29/30.) Using DAYS360(), always enter the date __after__ the last day for "end date". For example, DAYS360(DATE(2008,2,14),DATE(2008,3,1)) computes the number of days between Feb 14 and Feb 29 inclusive, based on a 30-day month. Can someone assist in compiling a single formula which would work for any set of dates during the year/s, including overlapping months say 14 Feb 08 to 17 Mar 08. DAYS360(DATE(2008,2,14),DATE(2008,3,18)) Note that I use Mar 18 for "end date", not Mar 17. Also, the DATE() expressions can be replaced by cell references that reflect start and end date. For example: DAYS360(A1, B1+1) |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Payrol calculations
On Mar 10, 7:19 am, sgl wrote:
StartDate EndDate 15 Feb 08 17 May 08 Pay is calculated for both days inclusive - 15 and 17 are calculated as pay days IMHO, day workers (like ship's crew) should be paid for the days that they work, not based on a common 30-day month. Otherwise, you are likely to have a mutiny on your hands ;-). But I am not a labor expert. If you are going to pay based on a common 30-day month, it seems that DAYS360 is the function for you to use, as I mentioned before. For your example, if the starting and ending dates are in A1 and B1 respectively, then: =1000 * days360(A1, B1+1) / 30 Note: Be sure to format the cell as Number or something similar. I should point out that in Excel 2003, at least, DAYS360 has some anomalous behavior. For example, if A1 is 2/27/2007 and B1 is 2/28/2007, then DAYS360(A1,B1+1) returns in 4. While that might seem strange, it is consistent with the notion of a common 30-day month. On the other hand, if A1 is 2/28/2007 and B1 is 2/28/2007, DAYS360(A1,B1+!) returns 1. While that might appeal to common sense, it is inconsistent with the previous result. In any case, you insist on taking a mixed approach: computing partial first and last months based on actual days divided by 30. As a consequence, someone who works 1 day less than a 28-day or 29-day month is short-changed, as is someone who works 31 days of a month compared to someone who works only 30 days that month. Be that as it may, the following formula is one solution that I believe matches your (dubious) computation. (There might be a simpler one.) =1000 * if(and(day(A1)=1,B1=eomonth(B1,0)), datedif(A1,B1+1,"m"), IF(eomonth(A1,0)=eomonth(B1,0), (B1-A1+1)/30, (eomonth(A1,0)-A1+1)/30 + (B1-eomonth(B1,-1))/30 + datedif(eomonth(A1,0)+1,eomonth(B1,-1)+1,"m"))) ----- original posting ----- On Mar 10, 7:19*am, sgl wrote: Thank you for your response. My arithmetic was completely off. My apologies. Perhaps I did not phrase my question correctly so here goes again. Assuming pay is 1,000 per month StartDate * * * *EndDate * * * 15 Feb 08 * * * 17 May 08 * * * Pay is calculated for both days inclusive - 15 and 17 are calculated as pay days *- in this example there is a complete calendar month of 31 days for March and therefore the pay is 1,000 - irrespective that the number of days is 31 - For February the crew member is entitled to 15 Days pay - 15 to 29 Feb 08 - 15 days/30 = 500.00 - irrespective that Feb has 29 days - For March the crew member is entitled to a full calendar month's pay of 1,000 irrespective that March has 31 days - For April the crew memebr is entitled to a full calendar month's pay of 1,000 - For May the crew member is entitled to 17 days pay/30 = 566.67 irrespective that May has 31 days In some instances the employment contract may be based on a calendar month basis - 1,000 per month irrespective of month days - which makes life a lot easier. My apologies for the confusion, I hope that I have made the problem clearer now. Thank you/sgl " wrote: "sgl" wrote: I am trying to compile a payroll for ships crew whose pay is calculated on a 30 day basis irrespective of calendar days in month. You probably want to use the DAYS360() function. *Be sure to read the Help page regarding US v. European assumptions. So for instance for the month of february 08, assuming a salary of 1,000, and the crew member signed on say 14 february his salary would be calculated on (29 days - 14 =15 days pay) 15/30 x 1,000 First, you have an off-by-one computation error. *If a person starts on Feb 14 and works through Feb 29, they have worked 16 days: *29 - 14 + 1. *(Proof: *If they onlyl Feb 14, we would compute 14 - 14 + 1, not 14 - 14; that is, one day, not zero.) Second, if they do work through Feb 29 and you want to compute pay "on a 30 day basis irrespective of calendar days in month", it seems to me that they should receive 17/30 of a month's pay, not 16/30. *(Proof: *If they start on Feb 1 and work through Feb 29, they should receive a full month's pay -- 30/30 -- not 29/30.) Using DAYS360(), always enter the date __after__ the last day for "end date". *For example, DAYS360(DATE(2008,2,14),DATE(2008,3,1)) computes the number of days between Feb 14 and Feb 29 inclusive, based on a 30-day month. Can someone assist in compiling a single formula which would work for any set of dates during the year/s, including overlapping months say 14 Feb 08 to 17 Mar 08. DAYS360(DATE(2008,2,14),DATE(2008,3,18)) Note that I use Mar 18 for "end date", not Mar 17. Also, the DATE() expressions can be replaced by cell references that reflect start and end date. *For example: DAYS360(A1, B1+1)- Hide quoted text - - Show quoted text - |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Calculations - Max and Min | Excel Worksheet Functions | |||
Calculations | Excel Discussion (Misc queries) | |||
calculations row by row | Excel Discussion (Misc queries) | |||
Help with PV calculations | Excel Worksheet Functions | |||
Calculations | Setting up and Configuration of Excel |