Formula Needed for Document Retention (I-9) files

[Aviaryinc]

I am trying to create a spreadsheet to maintain our I-9 forms, and would like to insert a formula to indicate on which date forms are due for destruction, which are past-due, etc.

Currently, C1 is designated as "Hire Date" and D1 is designated as "Term Date", E1 is "Retention Date"

Retention rules state that we must retain records for a minimum of 3 years after Hire Date OR 1 year after Term Date, whichever is later. (So, if employee is hired on 1-1-01 and terminated on 12-31-01, records must be kept until 12-31-04. If same employee is term'd on 1-1-14, records must be kept until 1-1-15)

Any suggestions?

 

[Aviaryinc]

The I-9 (Employment Eligibility Verification) form must be retained for 3 years after hire, or 1 year after release, which ever is longer. It goes date to date, not year to year, although we hold them to the end of the year and then do a purge. Not sure if it is for ease of administration or a "just in case" type of thing.

Note that I'm not the OP, just a guy that knows the I-9 requirements.

The formula outcomes DO in fact match the dates for retention that I received at formi9.com, which is what we have used to determine doc retention in the past...just double checked Maybe this will come in handy for someone else too!

Thank you bbott!!

 

[bbott]

You're welcome!

 

[Aviaryinc]

One more question: I want to also keep a separate spreadsheet for current (non-terminated) employees, so I need to insert a formula for those WITHOUT a term date, using ONLY hire date. If C1 = Hire Date, what is the recommended formula for D1 = Retention Date (Hire Date + 3 years)?

This is probably embarrassingly simple, but I can't seem to wrap my head around it... sigh :\

 

[Aviaryinc]

One more question: I want to also keep a separate spreadsheet for current (non-terminated) employees, so I need to insert a formula for those WITHOUT a term date, using ONLY hire date. If C1 = Hire Date, what is the recommended formula for D1 = Retention Date (Hire Date + 3 years)?

This is probably embarrassingly simple, but I can't seem to wrap my head around it... sigh :\

For anyone in need, I figured it out: =DATE(YEAR(C2)+3,MONTH(C2),DAY(C2))

 

[Shay1]

For anyone in need, I figured it out: =DATE(YEAR(C2)+3,MONTH(C2),DAY(C2))

I know this is an old post however it was very helpful!

Is it possible to add to the original equation (=IF(C2+1095>D2+365,C2+1095,D2+365)) If D2 is blank, meaning no termination date, then the results could also be blank?