Thank you for being a valued part of the CNET community. As of December 1, 2020, the forums are in read-only format. In early 2021, CNET Forums will no longer be available. We are grateful for the participation and advice you have provided to one another over the years.

Thanks,

CNET Support

General discussion

Excel Time of Service Calculation

Nov 20, 2004 3:56AM PST

I need to know how many years/months someone has been with the company - currently, I'm using:
=((YEAR(H2)-YEAR(B2))*12+MONTH(H2)-MONTH(B2))/12

wich works until hire and current months are the same but hire date is before current.

I mean, someone who hired in 11-10-03 by today (11-20-04) has been with the company 11 months (and change) - but with my formula this appears 1 year - correctly rounding up - but in this case, this is bad because it will make it appear as the employee's 1 year anniversary had already passed.

How can I keep it from rounding up?

Discussion is locked

- Collapse -
Re: Excel Time of Service Calculation
Nov 20, 2004 6:53AM PST

Use an if-function comparing the days:

=if (day of current-date>=day of hire-date, your formula, your formula minus something)

I think it's more difficult in fact to get it expressed as 1 yr and 2 months (your formula seems to give 1.16666666) but if you don't want to round up, this simple if-function, making a difference between "from 10 to 20 round-up and from 20 to 10 don't" should work. That's the difference you want to make, if I understand your problem correctly.
And, of course, the principle is useful for 1 yr-2 mnths notation also.

Hope this helps.


Kees

- Collapse -
Re: Excel Time of Service Calculation
Nov 20, 2004 6:28PM PST

Excel was right. 11-20-04 is 1 year and 10 days older than 11-10-03. Maybe what you meant was that he was hired 11-20-03 and your today is 11-10-04.

- Collapse -
Re: Excel Time of Service Calculation
Nov 20, 2004 9:11PM PST

You are right - I had it backwards - but I figured it out using Kees' hint:
=IF(AND(MONTH(B2)=MONTH(H2),DAY(B2)>DAY(H2)),((YEAR(H2)-YEAR(B2))*12+MONTH(H2)-MONTH(B2)-1)/12,((YEAR(H2)-YEAR(B2))*12+MONTH(H2)-MONTH(B2))/12)

- Collapse -
Re: Excel Time of Service Calculation
Nov 20, 2004 9:16PM PST

Wow - looking at this in FireFox, my formula does not wrap but it runs way out on the right out of the box, over the ads, into the yellow right border...........

So for fun I ran the HTML validator - that did not go well!

- Collapse -
Good job and glad I could help.
Nov 21, 2004 2:52AM PST

And I posted a link to your remark on the Feedback Forum. Maybe Lee can find some resources to fix the forum software.

Kees