Such a difficult function

[Delya]

Hi
Vacation days allowance are depend on 3 criteria for years/ say,
5 - 10 years=2 days
10-15 years=4 days
Over 15 days=6 days
Now, i have a list with years and months. Say-Q15 contains number "8 years
8 month 10 days". Columnd R contains 30 days (Main vacation allowance is 30
days) . In column S , I need to put in a cell formula calculating total
vacation days depending on above 3 criteria. eg. " if person worked more than
5 year , but up to 10 years, then 2 days are added to main vacation days
e.g.30+2=32 days, if not then, 30"/ However, i need to include in formula all
3 options.
Please help.

 

[Carl Witthoft]

Hi
Vacation days allowance are depend on 3 criteria for years/ say,
5 - 10 years=2 days
10-15 years=4 days
Over 15 days=6 days
Now, i have a list with years and months. Say-Q15 contains number "8 years
8 month 10 days". Columnd R contains 30 days (Main vacation allowance is 30
days) . In column S , I need to put in a cell formula calculating total
vacation days depending on above 3 criteria. eg. " if person worked more than
5 year , but up to 10 years, then 2 days are added to main vacation days
e.g.30+2=32 days, if not then, 30"/ However, i need to include in formula all
3 options.
Please help.

This is going to sound harsh, but this is serious advice. First of
all, this sort of bookkeeping is far better done in a database or in a
specialized calendar tool, rather than Excel.

That said, if you insist (or are required) to use Excel, start out by
reading all the Help information on Date and Time formatting.


After that, you have a basic choice: either use nested IF functions,
or spend a little time writing out a single equation that does what you
want. For example, assuming you've converted employment times into
pure years ( e.g. 5 years, 6 months --> 5.5 years), then

FLOOR( Years/5)*2 +30 = number of days' vacation.

And by the way, where can I get a job where the starting vacation time
is 6 weeks? Sounds fantastic!

Carl

 

[Delya]

Vacation rights become after 6 months that you started to work.( it differs on each country's regulations)

The formula you gave did not work.Plus i do not see that you use 3 options that is basically needed for calculation
I do not have specialized calendar tool, therefore need to use excel..

 

[John McGhie]

Hi Delya:

This is much easier if you store the employee's Employment Start Date in the
spreadsheet. You must somehow return the number of years of service
completed as an integer, and that's a lot easier if the date is already a
number.

Consider the following table:

Start Date Year Month Day Service(Yrs) Entitlement
11/5/2007 6 5 1 3 30
4/11/2004 9 11 24 6 32
6/20/2007 6 9 16 3 30
10/30/2008 5 5 6 2 30
11/17/1998 15 4 19 12 34
6/24/2001 12 9 11 9 32
9/14/2004 9 6 21 6 32
4/19/2005 8 11 16 5 32
3/14/2005 9 0 22 5 32
10/4/1998 15 6 2 12 34
8/12/1997 16 7 24 13 34
5/24/2000 13 10 12 10 34
6/19/2002 11 9 17 8 32
4/9/1998 15 11 27 12 34
1/21/2002 12 2 13 8 32
1/26/2006 8 2 8 4 30
10/3/2007 6 6 3 3 30
2/13/2001 13 1 20 9 32
1/9/2010 4 2 25 0 30
7/2/2004 9 9 3 6 32
9/8/2007 6 6 28 3 30
6/10/2001 12 9 25 9 32
4/22/2001 12 11 13 9 32
6/16/2006 7 9 20 4 30
1/1/2001 13 3 4 9 32
7/3/2009 4 9 2 1 30
1/5/2002 12 3 0 8 32
5/27/2009 4 10 9 1 30
8/11/1999 14 7 26 11 34
2/9/2002 12 1 25 8 32
5/28/2009 4 10 8 1 30
6/13/2002 11 9 23 8 32
4/18/1997 16 11 17 13 34
4/14/1997 16 11 21 13 34
7/25/1997 16 8 11 13 34
10/29/2000 13 5 7 10 34
5/25/2001 12 10 11 9 32
2/24/2004 10 1 10 6 32
1/11/2000 14 2 23 10 34
4/2/2008 6 0 3 2 30
7/9/2008 5 8 27 2 30
5/3/2006 7 11 3 4 30

I used a formula to derive the Start Date column from the raw data:
=TODAY()-(DATE(B3,C3,D3))

I need the start date as a real Excel date (a large integer) so I can
calculate with it.

Then I used a simple subtraction formula to derive the number of years of
service completed by each employee (for leave calculations, we're normally
not interested in pro rata entitlements, only 'completed years'):
=YEAR(TODAY())-YEAR(A3)

Then I used a FLOOR calculation to compute the leave entitlement:
=((FLOOR(E3,5)/5)*2)+30

You can, of course, string the whole thing together into a single formula if
you don't want to use the extra columns, Excel doesn't care:
=((FLOOR((YEAR(TODAY())-YEAR(A3)),5)/5)*2)+30

But I always make the formula up as separate bits first, so I can see what's
not working

Hope this helps

Vacation rights become after 6 months that you started to work.( it differs on
each country's regulations).

The formula you gave did not work.Plus i do not see that you use 3 options
that is basically needed for calculation.
I do not have specialized calendar tool, therefore need to use excel..

http://msgroups.net/microsoft.public.mac.office.excel/Such-a-difficult-functio>
n

This email is my business email -- Please do not email me about forum
matters unless you intend to pay!

 

[Delya]

HI Joh
I assume in your sample Columns B,C,D are Year,Month and day respectively. I tried your formula, but it did not give what i want.
I need somehow use IF function in order cell understands when it should add additional 2 days (or 4 or 6 days depending on work years) to basic vacation entitlement

Sample. Total work experience=10 years 2 months 5 day. According to regulation, over 10 years of work experience i need to add 4 days to basic vacation entitlement (basic=30 days), and it will give me 34 days total.Which excel formula i need to use..??

 

[John McGhie]

Hi Delya:

Your entire problem is with the way you have expressed "Length of Service".

Both Carl and I have told you this, three times. That quantity needs to be
a single integer. What you have is three different things (Years, Months,
and Days) in each cell, expressed in a language Excel can't read (English).

What you NEED is an Excel Date: 1683 is "8 years, 8 months and 10 days" in
the language Excel understands.

You have now discovered why one of the "Ten Commandments" of databases and
spreadsheets is "NEVER put more than one kind of data in a cell." And you
can see why.

You need to split your Length of Service column into three columns. How you
do that is up to you: I would use Microsoft Word to replace the words
"years", "months", and "days" with a tab, then paste the thing back into
Excel where it will turn into three columns.

Then you need to turn the three columns into an Excel Date. I sent you the
formula for that in the previous post. If I were you, I would go to the
next step at this stage, and store the column as the employee's Employment
Start Date, otherwise you will have this problem all over again next time
someone asks for leave. I sent you the formula for that too.

Cheers

HI John
I assume in your sample Columns B,C,D are Year,Month and day respectively. I
tried your formula, but it did not give what i want.
I need somehow use IF function in order cell understands when it should add
additional 2 days (or 4 or 6 days depending on work years) to basic vacation
entitlement.

Sample. Total work experience=10 years 2 months 5 day. According to
regulation, over 10 years of work experience i need to add 4 days to basic
vacation entitlement (basic=30 days), and it will give me 34 days total.Which
excel formula i need to use..??

http://msgroups.net/microsoft.public.mac.office.excel/Such-a-difficult-functio>
n

This email is my business email -- Please do not email me about forum
matters unless you intend to pay!

 

[CyberTaz]

Not to confuse the issue, but based on what you've described as your actual
need I think you're overcomplicating the worksheet by tracking months & days
in the first place. Here's just another thought...

Since the qualifying criteria is based on 'whole years plus whatever
fraction' the number of months & days is irrelevant. IOW, 10 years + 1 day
gets the same treatment as 10 years + 8 months + 2 days. Likewise, 12 years
+ 2 months + 11 days gets the same thing.

Why not just create a formula in the "Length of Service" column that
subtracts the Hire date from TODAY() expressed with 1 or 2 decimal places of
accuracy? It would make it much simpler to then create a lookup table that
would supply the appropriate addend to the base number of vacation days. Or
it would even simplify using John's suggestion as well as other options.

Regards |:>)
Bob Jones
[MVP] Office:Mac

HI John
I assume in your sample Columns B,C,D are Year,Month and day respectively. I
tried your formula, but it did not give what i want.
I need somehow use IF function in order cell understands when it should add
additional 2 days (or 4 or 6 days depending on work years) to basic vacation
entitlement.

Sample. Total work experience=10 years 2 months 5 day. According to
regulation, over 10 years of work experience i need to add 4 days to basic
vacation entitlement (basic=30 days), and it will give me 34 days total.Which
excel formula i need to use..??

http://msgroups.net/microsoft.public.mac.office.excel/Such-a-difficult-functio>
n

 

[Delya]

No, I cannot put formula using today()-start date, because from start date up today, there are many gaps. I am not sure if .xls takes into account gaps as well??
e.g. 01/01/1998-05/03/2001, then 07/09/2008-up todate.
Another problem- i used your formula =TODAY()-(DATE(B3,C3,D3)) , it gave me date 03/08/2001.( start date was 02.02.2009), why??

 

[John McGhie]

Excel will be quite happy with gaps in your period of service data if you
tell it about those gaps.

So you need to convert each start date into an Excel date, and each end date
into an Excel date. Then subtract each pair and simply add up the total.
Remember, in Excel a date is always a number of days (plus a decimal
fraction of a day, if you include a time).

For example, 01/01/1998 is 34334 to Excel. 05/03/2001 is 35493. Subtract
the two gives 1159 ‹ the number of days of service. Then 07/09/2008 to
today = 38785 - 38236 = 549. Add the two: 1159 + 549 = 1708 days.

Then you need to convert that back into years.

=INT(1708 / 365) = 4. We used INT there because we specifically wanted only
the years.

You don't need months for your current job, but if you did, it becomes
intensely complicated, since months have differing numbers of days in them.
Let's not go there.

It's a good idea to always use three letters for the month when handling
dates in Excel, so you are quite sure with is the month. For example to
Excel if you meant the Fifth of March, it's 35493; but if you mean the third
of May, it's 35552. This may be what has gone wrong with your formula: try
entering the dates with three letters for the Month: don't leave it to Excel
to guess, because there will be no indication when it guesses wrong.

Hope this helps

No, I cannot put formula using today()-start date, because from start date up
today, there are many gaps. I am not sure if .xls takes into account gaps as
well??
e.g. 01/01/1998-05/03/2001, then 07/09/2008-up todate..
Another problem- i used your formula =TODAY()-(DATE(B3,C3,D3)) , it gave me
date 03/08/2001.( start date was 02.02.2009), why??

http://msgroups.net/microsoft.public.mac.office.excel/Such-a-difficult-functio>
n

This email is my business email -- Please do not email me about forum
matters unless you intend to pay!