Hey auto,
I have nothing against using datedif...
Hi kmakifl,
Column B is Hire Date,
C, Annual,
D, Promotion Date,
E, 45 (Day Eval Due Date)
F, Status (days until due, 45 day eval)
G, 90 (Day Eval Due Date)
H, Status (for 90 days)
I, 180
J, Status (180)
K, 365
L, Status (365)
Hopefully got the meaning of those columns right. My words in parenthesis.
You have some kind of logic in your formula so that your evals aren't due exactly at n-days past hire date, but -- perhaps your are aiming for -- the same day of the week as the hire date, and will fall up to 5 days early to fall on that same day. i.e. cell E4 (45 day eval, employee a) has
Code:
=B4+40+WEEKDAY(B4,1)
If I got your aim there right, the formula is flawed. Consider employee B. Hired 1/18/2011, a Tuesday. The calculated 45-day eval date is 43 days later on 3/2/11. But 3/2/11 is a Wednesday. The problem might be worse for employees x and ac, had this formula been used. They would be scheduled for 2/20/2010, a Saturday (41 days after hiring. The actual 45-day date would be the following Wednesday, 2/24/2010). For employee h you use the formula [code]=B11+E3 which simply adds 45 days to the hire date, snagged from the column heading in E3, which is fine. That's a Saturday as it turns out too, but it's just a due date. A special calculation to end up on a weekend is odd to me though. The question is do you want perhaps the business day before the date as the eval date, or the business day after... or just list the date and figure it out when your weeks are planned.
For the exact day, you could take the lead from the formula for employee h, but use an absolute cell reference ($) so you can copy the formula to other rows and have it still refer to the column heading, plus check for a missing hire date and leave the eval date blank too in that case like this, for employee a:
[code]=IF(ISBLANK($B4),"",$B4+E$3)
(lock onto the hire date column for copying to 90/180/etc. hire date column with [code]$B, lock onto the number of days from the heading with the $3
.)
For the business day before (M-F, even holidays):
=IF(ISBLANK($B4),"",IF(WORKDAY($B4+E$3,0)=$B4+E$3,$B4+E$3,WORKDAY($B4+E$3,-1)))[/code]
(if the eval date would be a work day, use it, otherwise use the workday before.)
For the business day after (M-F, even holidays):
=IF(ISBLANK($B4),"",IF(WORKDAY($B4+E$3,0)=$B4+E$3,$B4+E$3,WORKDAY($B4+E$3,1)))[/code]
(if the eval date would be a work day, use it, otherwise use the workday after.)
Check the help for the WORKDAY and WORKDAY.INTL functions if you want to specify holidays or different work days. This function should simplify the calculation compared to trying to do calculations off of WEEKDAY.
These formulas could be copied to any cell in the 45, 90, 180, or 365 column and will calculate the appropriate date.
------
As to your calculations for the Status columns, which is what you were actually asking about, I recommend basing off of the 45-90-180-365 due dates as listed in those respective columns.
Your formula, as it looks in cell F4 (Status for 45-Day, employee a):
=IF(AND(E4>(B4-5),E4-NOW()>0),E4-NOW(),""
[/code]
Could be read:
If the 45-Day_Evaluation_Date is subsequent to 5 days before Hiring and the timespan between the 45-Day_Evaluation_Date and NOW is more that 0 days (i.e. the 45-day_eval_date is in the future), then indicate the timespan for status, otherwise, indicate nothing.
Won't the 45-Day eval ALWAYS be subsequent to 5 days before hiring? Heck, I doubt it's even ever before hiring at all. This check seems to relate to the unusual logic for calculating the eval date discussed above, but I don't think any of that logic is required here, and since evals are always after hires, it serves no purpose as written.
If we eliminate that, we have:
Code:
=IF(E4-NOW()>0,E4-NOW(),"")
Which is OK, but it's good practice to use TODAY() instead of NOW() when you're not interested in the time of day. You could get unexpected results in some calculations, otherwise.
So,
[code]=IF(E4-TODAY()>0,E4-TODAY(),"")
OK, but for employee d, this results in a #VALUE error.
If you select cell F7 (Status for employee d), where the error is, you'll see an exclamation mark button appear next to the cell. Click on it, then click on "Show calculation steps" and you'll see it underline the first part of the formula it evaluated, [code]E7-TODAY() in [code]E7-TODAY()>0, which evaluates to [code]""-40830
. Since the 45-day eval date is missing for that employee, it is trying to subtract today's date from an empty text, which is impossible.
There are several solutions. The simplest is to use the N(x) function which will give you x if x is a number, otherwise it will give you 0. N(E7) will do the same for E7, the eval date. 0-TODAY will of course be a negative number, but anything <= 0 is already discarded by your formula, so this is harmless.
This would give us, back in row 4, employee a:
=IF(N(E4)-TODAY()>0,E4-TODAY(),""
[/code]
Copy this formula to all cells in all Status columns, and you will have either a number or a blank, but no errors.
Apply the custom number format to all the status cells that I mentioned in my first post:
# "days left"[/code]
And it will include the words "days left" after the displayed number.
If you decide to add a column for when the 45/90/180/365 day evals were actually done, or at lease IF they were actually done, you could do a couple things. You could change this formula to a version that allows negatives (when an eval date is listed of course, and only if no eval has been completed), and then you could change your custom format to something like:
# "days left";[Red]# "days overdue"[/code]
Which will put negatives in red with the word "overdue" instead of a minus sign.
I see you have color key with a background red for past due and background pink for due within 2 weeks. To accomplish that, you'd have to use a conditional formatting rule.
Change the cell custom number format to:
# "days left";# "days overdue"[/code]
(color will be handled by conditional formatting)
Then you'd create two conditional formatting rules for the status cells. Highlight the status cell>conditional formatting>highlight cell rules>Between...> and then specify a low range of 0 and high of 14, and select a pink fill. Similarly with the same range selected, >highlight cell rules>Less Than>0>red fill.
Hope this was informative.
Asa
