Analytics Software

Hi folks,

I was wondering if anyone might have the answer to a little employee length of service conundrum. I'm trying to construct a calculation that will calculate an employee's length of service in whole Years worked only. I also need to produce a second calculation to calculate length of service in whole months worked only but with the whole years worth of months deducted from it.

For example if a persons hire date is 05-Feb-2001 the two calculations would return:

Length of service Years 7
Length of service Months 6

The following calc will generate length of service as a decimalised number:

MONTHS_BETWEEN(SYSDATE,Hire Date) /12

So for my example person I get 7.64 years.

I need to somehow round this down to the nearest 1 to get whole years service. I cannot use the ROUND function because it rounds to the nearest, so when a number is n.5 or higher it'll round up and will only round down when the number is n.49 or lower. grrr.

My next mission is to work out months service based on whats after the decimal place. If I could round the Length of service Years down I think I could achieve this. I would just do:

ROUNDDOWN(((MONTHS_BETWEEN(SYSDATE,Hire Date) /12) - (+ROUNDDOWN+(MONTHS_BETWEEN(SYSDATE,Hire Date) /12))*12)

ROUNDOWN being my non-existent function!

Any advice on this is greatly appreciated.

Lloyd