Time Based Formula

  • Thread starter Thread starter Dave
  • Start date Start date
D

Dave

I would like to find a formula that gives a specific
output based on the amount time past.

Inputs would be the Current Date & Starting Date.

Outputs would be (if difference is between 0 & 5 years,
10) (if difference is between 6 & 10, 15).....so on & so
forth.

I haven't a clue of where to start, could someone give me
some help?

Thanks
Dave Schenk
 
Use a VLOOKUP table with 3 columns:
First column type in 0, second column type 5, third type 10
Second row first column type 6, second row second column type 10, second row
third number type 15. Name it MyTable

in A1 insert the number 3 and in B1 the following VLOOKUP formula:
=VLOOKUP(A1,mytable,3).

For any number in A1 between 0 and 5 a 10 shall be returned.

HTH

Gilles Desjardins
 
I'm not quite sure how to set the dates against your array. please let
me explain a little further. this is being used to determine the
number of days of vaction someone is eligible for different lengths of
service

The inputs are going to be dates. Start of service date and todays
date. the number would have to be determined by these dates... how
would I get this into the formula?


Thanks I appreciate you looking at this
Dave
 
Hi Dave, the table is a Vlookup table. If I understand your comment a person
having worked at your company from 0 years to 5 years gets 10 days holidays.
OK

So, in A1 start date of the person, in B1 today's date, in C1 Datedif OR
Networkdays AND in D1 the Vlookup FORMULAthat refers to the Vlookup TABLE.
It should look something like this:
A1 B1 C1
D1

29 Jan 1997 19 Nov 2003 =datedif(A1,B1,"y")
=Vlookup(C1,mytable,3)

Hope this helps a bit more, if not come back.

Gilles
P.S. use Networkdays if you wish to count working days only but not weekends
or holidays
 
OK! So far so good. I understand this much of it and it works fine,
however I still have a small problem. is there a way to have it work
for a range of time. right now it works fine it your start date and
current date equal exactly one of the lengths of service defined.

This is the full vday chart

Milestone Vacation Days
1 5
3 7.5
5 10
8 12.5
10 15
13 17.5
15 20
20 25


So someone with 3 years gets 7.5 Vdays until their 5th year in which
they get 10 Vdays for the next three years and so on...

so how would I adjust the formula to meet a range of time


I did just think of something but would still like to see if there is
a range solution

I could add every in between milestone


1 5
2 5
3 7.5
4 7.5
5 10 ......


Thank you by the way Gilles,
You have been very helpful!
Dave
 
One way

=IF(A1<1,"no vacation
yet",VLOOKUP(A1,{1,5;3,7.5;5,10;8,12.5;10,15;13,17.5;15,20;20,25},2))

assuming that 2 years should return 5 for instance. Having said that I must
says to wait 20 years to get
25 days vacation is rather primitive. Even China has better vacations than
US..
 
Acually I made a mistake in the number of vacation days (which was
already fixed) it actually starts out at 10 days but still ends at 25
days.....

But to reply to your comment. Five weeks vacation isn't too terribly
bad when you add in the manditory three weeks for holidays. that's 2
months out of the year.....with full pay.

and in China the people get paid very little money.....vacations are a
burden!

Anyway thank you very much for your help. I've made my adjustments
and am going to plug this in and check it out right away!

Thanks again
Dave
 
I get the same error with this function. it doesn't pick up the in
between years unless you type every one of them in.

Thanks
Dave
 
Back
Top