ROUNDUP Formula calculating incorrectly

[busdriverbrian]

Hello. Brand new to this forum as I have exhausted my research on the web for a solution.

I am using the following formula to calculate the period between 2 dates and then ROUNDUP to the next whole number in years with a goal of employee seniority measured in years (< 1 year equals 1, >= 1 but <2 equals 2, etc.) Basically, the result should return 3 on the 2 year anniversary date of an employee's hiring.

=ROUNDUP(((YEAR(A10)-YEAR($B$2))*12+MONTH(A10)-MONTH($B$2))/12,0)

$B$2 = 1/2/2006

In this example, when A10 = 1/1/2012 the result is 6

When A10 = 1/2/2012 the result is also 6 which would be expected. I guess I am looking to add 1 year to a result that would be a whole number prior to the application of ROUNDUP.

HOW CAN I MODIFY THIS FORMULA TO ENSURE THE EXAMPLE WITH A10=1/2/2012 RETURNS A RESULT OF 7 INSTEAD OF 6?

Thanks in advance for any help you folks can provide.

Regards,

Brian

[Pepe Le Mokko]

Perhaps = DATEDIF(B2,A10,"Y")+1

[Glenn Kennedy]

It's because your criteria are set up to count "<1 year" as "Year 1", not year zero. Modify your equation:

=ROUNDUP(((YEAR(A10)+1-YEAR($B$2))*12+MONTH(A10)-MONTH($B$2))/12,0)

[busdriverbrian]

Thanks Glenn. I actually do want "<1 year" as "Year 1", but want "Year => 1 < 2" to give a result of "Year 2".

Essentially, a person is hired on 1/2/2013 and will be considered "Year 1" until their hire anniversary date on 1/2/2014 at which time they will be considered "Year 2." My original formula works properly accept when rounding up the the same dates on different years. Rounding up the integer 2 results in 2 when I want it to be 3. Thanks again for the help.

[benishiryo]

welcome to the forum. did you try Pepe's formula in post #2? just add the dollar signs if you need to for cell B2:
=DATEDIF($B$2,A10,"Y")+1

this calculates the complete number of years between 2 dates. he then adds 1 to it because you want it rounded up regardless of how many incomplete months there are.

[busdriverbrian]

It looks like Pepe's solution did the trick. Thank you to everyone for your help on this. Much appreciated.

Brian

[Pepe Le Mokko]

welcome to the forum. did you try Pepe's formula in post #2? just add the dollar signs if you need to for cell B2:
=DATEDIF($B$2,A10,"Y")+1

this calculates the complete number of years between 2 dates. he then adds 1 to it because you want it rounded up regardless of how many incomplete months there are.

Thanks Beni. I've often seen that OP's tend to look at the last post, without checking out the others . Why? I don't know

[benishiryo]

@Pepe:
you're welcome. i encountered that myself countless of times too.


@Brian:
If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.