Formula for Evaluations Due and Past Due Dates

**Mimi8** · 03-10-2014, 02:13 PM

I have a simple table for tracking performance evaluations with 3 columns:
Employee Name (A), Date of Hire (B), Eval on File (C)
Evaluations are completed 6 months from Date of Hire (1 time) and on an annual basis and logged into column C.
I need to identify a list of evaluations that 30 days past due from the beginning of each month on a monthly basis and having a really hard time figuring it out.

I spent hours breaking the process down into steps (by adding extra columns to calculate 6 month due date, difference between current date and 6 month, due date for current year, and it still takes a long long long time.

I can't help but think there must be an elegant formula there that can tie it all together. I worked hours to research it and failed :-(((((

Could you help me, please?

I put a scenario below to illustrate my table. As of 02/01/14, Anna Li's 6 month evaluation is past due, James and Johns' annual evals are past due as well.

Employee Name DOH Perf Eval
Anna Li 6/8/2013 No
James Hawkins 12/12/2006 12/12/2012
John Silver 1/8/2007 1/8/2013
Kim Woo 8/14/2007 8/14/2013
Sam Shell 6/6/2005 6/6/2013
Tanya Smith 7/1/2013 1/1/2014

At my wit's end,,,

**daddylonglegs** · 03-10-2014, 08:51 PM

Perhaps try like this:

Put your test date in F1 then in D2 copied down for next evaluation date use this formula

=IF(ISNUMBER(C2),EDATE(C2,12),EDATE(B2,6))

[EDATE requires Analysis ToolPak to be installed if you are using Excel 2003]

and in E2 copied down use this formula for days past due (if any)

=MAX(0,F$1-D2)

I also used conditional formatting on that column to format cells which are > 30, see attached

**Mimi8** · 03-11-2014, 04:52 PM

daddylonglegs, I just tested it on my spreadsheet - WOW. You are AMAZING!
Thank you very much for this solution!!!
I can not describe how appreciative I am (thinking back on all the days I spent on trying to resolve it) and how impressed I am with these beautiful formulas!
BRAVO!!!!