Hour's pay calculations

[roandr]

I have read a few of the answers in some other posts and I'm almos
afraid to ask my questions because I might not understand the response
Nevertheless, I am going to try.

I am paid by the hour where I work. The hourly pay is not consisten
but based on what day and time of day I work.

For example;

For the sake of the argument (pun intended though probably no
understood), let us say the following values apply;

$15 an hour from 08:00 to 13:00
$21 from 13:00 - 16:00
$26 from 16:00
$30 for all hours beyond the eight

(These values vary from day to day within a week but all weeks have th
same scheme, except from official holidays… that’s going to be a majo
headache to implement I think).

I want to make a "chart" where the input is a date, the starting hou
and the ending hour. The output should be my earnings for that day.

Now, do not get me wrong. I just want pointers as how to set this up
What functions I should look at and why (I actually want to lear
this). I do not want spoilers... well, not at first anyways...

Note: I am using Excel 2003

 

[Frank Kabel]

Hi
to give you some spoilers: For calculating your pay you can use a
combination of MAX and date subtraction. e.g.
if A1 stores your start time and B1 your end time (not fully tested -
but it should give you an idea):
=(A1<TIME(13,0,0))*(MIN(B1,TIME(13,0,0))-A1)*15 +
(A1<TIME(16,0,0))*(B1>TIME(13,0,0))*(MIN(B1,TIME(16,0,0))-TIME(13,0,0))
*21+
(B1>TIME(16,0,0))*(B1-MAX(TIME(16,0,0),A1))*26+
MAX(B1-A1-TIME(8,0,0),0)*4

Holidays are another thing. If you provide a little bit more detail how
this will affect your hourly rates (e.g. fixed additional amount,
percentage, etc.) I can give you some ideas for that, too

Frank

 

[roandr]

Thank you for your quick response.

I will have to take your formula apart and learn the functions

 

[Ken Wright]

roan - some of the responses can often look a little intimidating, so never
worry about having to ask how a formula is working, or even just a piece of a
formula. We want to make sure you get sorted, and if you show an interest in
actually understanding the solution as well, you will have no shortage of people
willing to step in and help out with advice and explanation.

 

[roandr]

Ken Wright,

Thats good to know because I am having difficulties in my approach o
the problem.

After having disected Frank Kabels formula I've got a goo
understanding of the individual functions, even how a few of them wor
togheter. But trying to understand it as a whole I can't. Im stuck wit
a feeling that I've missed something, like the logic of the syntax. Wh
is it build that way?

I mean, how do you begin addressing the problem? Where do you start? I
there a general opinion as how to build such a formula?

Does my questions make any sense or am I just plain stupid ? ;

 

[Frank Kabel]

Hi
1. your kind of problem is IMHO quite difficult to put into formulas.
That is: they are getting complex. So in some cases it could be better
to write a specific macro/UDF.
2. The best thing if you can't understand a part of the formula is to
come back here and ask - just as you did. As I provided the first
solution 8and not necessary the best one) I'll give my best to explain
it part by part. And if you still have questions just ask again...

so lets start with the formula (by the way, did it work for you) I
provided to you
=(A1<TIME(13,0,0))*(MIN(B1,TIME(13,0,0))-A1)*15 +
(A1<TIME(16,0,0))*(B1>TIME(13,0,0))*(MIN(B1,TIME(16,0,0))-TIME(13,0,0))
*21+
(B1>TIME(16,0,0))*(B1-MAX(TIME(16,0,0),A1))*26+
MAX(B1-A1-TIME(8,0,0),0)*4

The best way to analyse formulas (MHO) is to start from inner-most
part. the above formula consist of 4 parts which are added:
a) wage for 08:00 - 13:00
b) wage for 13:00 - 16:00
c) wage after 16:00
d) overtime wage (after 8 hours)

lets start with a) (A1: starting time; B1 ending time)
=(A1<TIME(13,0,0))*(MIN(B1,TIME(13,0,0))-A1)*15
the first part (A1<TIME(..)) checks if the starting time is before
13:00. This part return TRUE or FALSE. As this result is multiplied
with another expression Excel converts these boolean values (TRUE = 1;
FALSE=0). So if your starting time is not before 13:00 then this part
returns FALSE and '0' respectively. Therefore in this case the whole
part is 0.
If your starting time is before 13:00 then the second part (MIN(...))
calculates the duration for this period:
To make the formula shorter (though more complex at the same time) I
used MIN. The MIN function return the smaller value of B1 and 13:00. So
if you have worked longer than 13:00 only 13:00 will be returned. If
shorter then B1 is returned. After that A1 is subtracted from this
value. some examples:
B1: 14:00 -> MIN(..) returns 13:00
B1: 12:30 -> MIN(..) returns 12:30

The result is multiplied with your hourly wage (and there is my first
error). You have to multiply it with 24 (as Excel stores times as
values and 24 are equal to 1 -> 1 hour in Excel is 1/24).

Now part b): Quite similar. one addition: i had to check that your
starting time is before 16:00 and that your ending time is after 13:00
part c) is also quite simila
part d) calculates your overtime wage. I made the following assumption:
Overtime can only occur after 16:00 (as 08:00 seems to be the earliest
starting time). Therefore the overtime wage will wlways be 30$ instead
of 26$ and therefore always an additional 4$. So the last part
calculates the hours > 8 hours (if this return a negative value a '0'
is returned by the MIN function. This value is multiplied with 4

So as I mentioned above i made a mistake in the formula (forgot the
'*24' part) change it to
=(A1<TIME(13,0,0))*(MIN(B1,TIME(13,0,0))-A1)*15*24+
(A1<TIME(16,0,0))*(B1>TIME(13,0,0))*(MIN(B1,TIME(16,0,0))-TIME(13,0,0))
*21*24+
(B1>TIME(16,0,0))*(B1-MAX(TIME(16,0,0),A1))*26*24+
MAX(B1-A1-TIME(8,0,0),0)*4*24

I hope this has helped you a little bit. As said above: If you have
more questions just ask (These are then definitetly due to my 'bad'
explanantions...)

 

[roandr]

-The best way to analyse formulas (MHO) is to start from inner-most
part.-

This is actually what Im looking for. Could you please elaborate on
this, or maybe direct me to some good source of information explaining
the "dynamics" of a "complex" formula?

-so lets start with the formula (by the way, did it work for you) I
provided to you-

It was of great help. Not only did it work (after doing a couple of
minor corrections, as you yourself have pointed out), but I got to
tinker with TIME, MIN and MAX alot I also discovered I'd forgotten
some info that should have been given in my original post (to make it
even more complex). More on that further down...

I spent an hour disecting your formula earlier, fiddling with the
different functions, until I got the meaning of it. Your excellent
explenation is, more or less, my earlier revelation.

I forgot to tell you that the values I gave you is only correct if we'r
talking about monday through friday. Saturday and Sunday has a
different tariff. Sunday is easy cause it gives a flat tariff all day,
but saturday is similar to the weekdays though the tariff is staggerd.
So the formula needs an addition. It needs to pick the right day from
the date as well. I've played around with the different time functions,
like WEEKDAY etc but implementing this in the already complex formula
makes my head hurt.

So back to my first question: How do I learn to write complex formulas?
Only through trial and error... or is it some kind of general consensus
regarding this?

Best regards
Ronny Andreassen

 

[Frank Kabel]

Hi Ronny
see below
[snip]

This is actually what Im looking for. Could you please elaborate on
this, or maybe direct me to some good source of information explaining
the "dynamics" of a "complex" formula?

you may have a look at http://j-walk.com/ss/books/xlbook24.htm for a
good book about this topic (IMHO)

[snip]

I forgot to tell you that the values I gave you is only correct if
we'r talking about monday through friday. Saturday and Sunday has a
different tariff. Sunday is easy cause it gives a flat tariff all day,
but saturday is similar to the weekdays though the tariff is staggerd.
So the formula needs an addition. It needs to pick the right day from
the date as well. I've played around with the different time
functions, like WEEKDAY etc but implementing this in the already
complex formula makes my head hurt.

O.K. now its getting complex
you may try something like

=IF(WEEKDAY(A1,2)<6,(A1<TIME(13,0,0))*(MIN(B1,TIME(13,0,0))-A1)*15 +
(A1<TIME(16,0,0))*(B1>TIME(13,0,0))*(MIN(B1,TIME(16,0,0))-TIME(13,0,0))
*21+
(B1>TIME(16,0,0))*(B1-MAX(TIME(16,0,0),A1))*26+
MAX(B1-A1-TIME(8,0,0),0)*4),
IF(WEEKDAY(A1,2)=7,(B1-A1)*24*flat_fee,formula for saturday))

Frank

 

[roandr]

http://j-walk.com/ss/books/xlbook24.htm

From the tables of content it looks like a perfect fit.

By the way, your addition to the formula will be scrutinised ASAP

Thanks alot man. You've been of great help!

 

[Frank Kabel]

Hi Ronny
thanks for the feedback

 

[Ken Wright]

http://j-walk.com/ss/excel/usertips/tip079.htm

The following will give you an example of this very topic from John's book. The
book is well worth buying though, and covers a huge number of topics.

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------

Hi Ronny
see below
[snip]

This is actually what Im looking for. Could you please elaborate on
this, or maybe direct me to some good source of information explaining
the "dynamics" of a "complex" formula?

you may have a look at http://j-walk.com/ss/books/xlbook24.htm for a
good book about this topic (IMHO)

[snip]

I forgot to tell you that the values I gave you is only correct if
we'r talking about monday through friday. Saturday and Sunday has a
different tariff. Sunday is easy cause it gives a flat tariff all day,
but saturday is similar to the weekdays though the tariff is staggerd.
So the formula needs an addition. It needs to pick the right day from
the date as well. I've played around with the different time
functions, like WEEKDAY etc but implementing this in the already
complex formula makes my head hurt.

O.K. now its getting complex
you may try something like

=IF(WEEKDAY(A1,2)<6,(A1<TIME(13,0,0))*(MIN(B1,TIME(13,0,0))-A1)*15 +
(A1<TIME(16,0,0))*(B1>TIME(13,0,0))*(MIN(B1,TIME(16,0,0))-TIME(13,0,0))
*21+
(B1>TIME(16,0,0))*(B1-MAX(TIME(16,0,0),A1))*26+
MAX(B1-A1-TIME(8,0,0),0)*4),
IF(WEEKDAY(A1,2)=7,(B1-A1)*24*flat_fee,formula for saturday))

Frank

 

[roandr]

Ken Wright,

Seems very easy to understand contrary to the final words of the tip;

-Keep in mind that a complex formula such as this is virtuall
impossible to understand. Therefore, use this type of formula only whe
you are absolutely certain that it works correctly and you are sur
that you will never need to modify it in the future. Better yet, keep
copy of those intermediate formulas -- just in case.-

My excel-nerve is itching already ;

 

[Ken Wright]

LOL - I have created numerous large formulas at work, and whilst when I am doing
it I know what each piece is doing, if I have to come back to it a week or more
later to change it for any reason, I usually have to try and dissect the damn
thing to work out just what the hell I was doing when I did it. That's all I
think John was really getting at.