Excel formula question

DrPhil

Outstanding Member
Joined
Jul 19, 2006
Messages
29,378
Reaction score
20,036
Points
7,907
Location
Ireland
I'm having a brain fart this morning, as I'm sure I've done this before or similar.

I have a spreadsheet with staff start dates. I need a formula to take each staff member and categorise them according to that date.

Either <5, 5-10 or >10

So I need a formula that looks at their start date and tells me at a glance which category they are in. I can easily colour format once the formula applies the result.

Help?
 
Do you mean how many years since their start?

(Cell - Today())/365 will give you the number of years for a start

Or better still use the function YEARFRAC(Cell, Today())

Cheers,

Nigel
 
It depends on how the length of service is calculated. If it's based on exact anniversary, I suggest you use total days rather than years:

=IF((TODAY() - DoJ) < 1826, "LT5", IF((TODAY() - DoJ) > 3652, "GT10", "5-10"))

That covers leap years. There is always 1 leap year in any period of 5 years, and 2 in 10 (until 2100, anyway).
 
PS: I suggest you make sure it will do what you want. Any formula will recalculate every time you open the spreadsheet, (unless you turn auto-recalc off), in which case it will not be the same from day to day. So an employee who was 5-10 yesterday might become <5 today. At first glance that sounds OK, but is it?
 
PPS: No, I'm wrong about leap years. There could be 2 in 5 years and 3 in 10.

Retires to rethink!! :blush:
 
This is for when the ward manager calculates the coming year's annual leave entitlement.

She (or I) will be sending the information by a mail merge, to generate a sheet for each staff member.

The merge will produce a document with:
Name
Grade
Years of service

Rather than the 3rd option giving their start date, she wants it to just say 0-5, 5-10 or 10+.

There will of course be a small number whose incremental date lands during the coming year but they are a small enough group that she will know them and check for them.
 

The latest video from AVForums

Is 4K Blu-ray Worth It?
Subscribe to our YouTube channel
Back
Top Bottom