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?

 

[bbott]

Welcome to the board.

If the hire date is 1-1-01, three years from that date would be 1-1-04, but you're saying the records need to be kept until 12-31-04. Do they need to be kept until the end of the calendar year or three years from hire date?

 

[Aviaryinc]

Ah, good catch! Yes, you are right, if hire date is 1-1-01 and term date is 12-31-01, retention date would be 1-1-04 (thank you!).

 

[bbott]

Ah, good catch! Yes, you are right, if hire date is 1-1-01 and term date is 12-31-01, retention date would be 1-1-04 (thank you!).

OK, then I think this should work for you. Enter this formula in E1 and copy down:

=IF(C2+1095>D2+365,C2+1095,D2+365)

 

[MarkAMcCain]

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.

 

[Aviaryinc]

OK, then I think this should work for you. Enter this formula in E1 and copy down:

=IF(C2+1095>D2+365,C2+1095,D2+365)

Not sure why, but that doesn't work. It works for the initial row (C2 - E2) but not for the subsequent rows. Just copied & pasted as suggested.

 

[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.

You are right - and I posted the same parameters in my initial post. Still, per the site formi9.com,the dates mentioned above are all correct. I'll do some comparison with their site and the outcomes from the formula and post back with the results.

Thanks for the input!

 

[Aviaryinc]

Not sure why, but that doesn't work. It works for the initial row (C2 - E2) but not for the subsequent rows. Just copied & pasted as suggested.

Nevermind --- I figured it out (pasted into E2 & copied down)

 

[bbott]

Not sure why, but that doesn't work. It works for the initial row (C2 - E2) but not for the subsequent rows. Just copied & pasted as suggested.

What are the dates in C3 and D3?
What date appears in E4?

 

[bbott]

Nevermind --- I figured it out (pasted into E2 & copied down)

OK, good!