Excel 1 vs 10 in Excel Formula

Mar 5, 2015
Reaction score

I'm trying to categorize length of service into groups so I can create a report and graph. I wrote an IF formula that seems to be working EXCEPT if an employee's length of service is a double digit year instead of single. So, 10 is calculating as 1, 20 as 2, 40 as 4...etc.

Here is an example of the formula and formatting of data I'm using - any ideas as to if I can fix this or need to take a whole different approach?

=IF(F160>"5 Years 1 Month","Over 5 Years",IF(F160>="4 Years 1 Month","Up to 5 Years",IF(F160>="3 Years 1 Month","Up to 4 Years",IF(F160>="2 Years 1 Month","Up to 3 Years",IF(F160>="1 Year 1 Month","Up to 2 Years",IF(F160>="0 Years 6 Months","6 to 12 months",IF(F160>="0 Years 0 Months","Less than 6 months","Less than 6 Months")))))))

Here is an example of the values in the F column (formatted as "General") shows as such:
0 Years 10 Months
1 Year 9 Months
10 Years 1 Month
24 Years 11 Months
3 Years 11 Months

Sorry if there is a better way to format this post and give a visual but this is my first post.

Any help is greatly appreciated!


Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question