Payrol calculations

[sgl]

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

 

[Mike H]

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

 

[Mike H]

forgot to mention, start date in a1, salary in b1

 

[joeu2004]

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)

 

[joeu2004]

Errata....

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.

 

[sgl]

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

 

[joeu2004]

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

 

[joeu2004]

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 -----