Creating a less than or greater than formula

  • Thread starter Thread starter FB
  • Start date Start date
F

FB

Can anyone help me with this:
I need to create a spreadsheet that works out staff length
of service and which catergory they fall into. i.e. <6
months, >6mths but < 2 years etc. I can figure out how to
get the spreadsheet to calculate length of service but
then I need to get it to say "1" in the correct box. Can
anyone help.
Thank you.
 
Hi

=DATEDIF(HireDate,TODAY(),"y") & " years" & DATEDIF(HireDate,TODAY(),"ym") &
" months"
is the easiest one, but it'll be wise to add checks for cases the number of
years and monthe are 0 or 1.
 
One way:

Set-up this table in Sheet2, A1:B5
----------------------------------
0 < 6 mths
180 >= 6 mths to < 1 year
360 >= 1 year to < 1.5 years
540 >= 1.5 years to < 2 years
720 >= 2 years

Above assumes simple approximations
of 1 month = 30 days, or 180 days = 0.5 year

In Sheet1
---------
Supposing you have set-up in A1:I1,
the col headers:

Name
Start-date
End-date
Days of service
< 6 mths
= 6 mths to < 1 year
= 1 year to < 1.5 years
= 1.5 years to < 2 years
= 2 years
Click to expand...

Put in D2: =C2-B2
Format D2 as number

Put in E2:
=IF(VLOOKUP($D2,Sheet2!$A$1:$B$5,2,TRUE)=E1,1,"")

Copy E2 across to I2,
then copy down as many rows
as there is data in cols A - C

"1" will be placed in the correct box in one of
cols E to I according to the number of days
of service calculated in col D

--
Rgds
Max
xl 97
 
Back
Top