EXCEL FORMULA FOR DATES

Question

I just want to ask an excel formula for this.

If an employee exceeds one year of employment he will have a free uniform

If he was hired last October 1, 2015 and he requests for new uniform this October 22, 2016 then he should have free uniform.

10/1/2015 - 10/22/2016 = "Can Avail Free Uniform" or "Cannot Avail!"

I just want the two optional answers to reflect using an excel formula.

Thanks

Answer 1

I would use these four columns

and use the following formula in D2:

=IF(AND((C2-A2)/365>=1,(C2-B2)/365>=1),"Can avail free uniform","Cannot avail")

This checks to see of the time since date of hire AND the time since the previous request is more than 1 year and returns "Can avail free uniform" if both equals ore are greater than 1, and returns "Cannot avail" if one of them is less than 1.

Answer 2

If I would create a formula like this:

I would use 4 cell columns: Date Hired,Date Request,Years of Service,isAvail

On my Years of Service, I would use DateDif Function, Where =(DateDif('cella','cellb',"m")/12) in order to achieve a year. Then on isAvail column, I will use =IIF('cellc >= 1', "Can Avail","Cannot Avail")

See if it helps.