Formula to calculate time base on a date

[Greed]

Hi all- this is a re-post (post - 2689573) since I did not have any luck the first time around,

For the purpose of automating vacation time allotment - I am looking for a way to calculate the number of years between dates (ie. the start date of an employee to present) - catch is, the number of applicable 'years' change if the start date is before June 30 Additionally, a new year's worth of vacation time is accrued on January 1st of each year.

Example - an employee who started on June 30 2011 qualifies for 1 year's worth of vacation by the next day (July 1) and then another year come Jan 1 of the following year....an employee who starts on July 1 does not qualify for 1 year's worth of vacation until January 1 of the following year...hope this makes sense - sample attached
TIA

[Bob Phillips]

Try this

=(SUMPRODUCT(--(TEXT(ROW(INDIRECT(A2&":"&TODAY())),"ddmm")="0101"))+(MONTH(A2)<7))*8

[TMS]

Well, you can get the number of months using:

=DATEDIF(A2,$D$1,"m")/12

and you can round that to years with:

=ROUND(DATEDIF(A2,$D$1,"m")/12,0)

And you can determine whether or not to add 1 using:

=IF(MONTH(A2)<7,1,0)

So, putting them together:

=ROUND(DATEDIF(A2,$D$1,"m")/12,0)+IF(MONTH(A2)<7,1,0)


However, I don't get the same answers as you so you may need to tweak the rounding


Hope this helps, TMS

[Greed]

Nifty - thanks Bob!

[TMS]

@Bob: yes, that is very clever.

Regards, TMS