Forum Discussion
Excel Formulas
So, Can I make the value of AJ7 be 0 if there is no data in B7...
I can then use conditional formatting to hide the 0....
The formula works great. Unfortunately, I have run into another issue. This needs to be conditional on an employee hire date. I have added a new column (Start Date) to the Employee Names sheet. Employees can't accrue Perfect Attendance points prior to their "Start Date". So if the employee starts in April... then Jan - Mar would not have points in the Perfect Attendance column.
OK. I have entered Employee 1 and Employee 2:
Employee 1 Started 6/1/2108.
They get .5 points for each month that they have perfect attendance.
So 1.5 points total for Jan/Feb/Mar.
If they receive an attendance violation in Apr -- the violation gets reduced by the accrued perfect attendance points.
Employee 2 Started 3/1/2019.
They get .5 points for each month that they have perfect attendance.
They had perfect attendance in Mar.
So .5 points total for Mar.
If Employee 2 receives an attendance violation in Apr -- the violation gets reduced by the accrued perfect attendance points.
However, since employee 2 was not employed in Jan/Feb, they are not entitled to perfect attendance points for Jan/Feb.
So - we need to modify our formula so that it does not award perfect attendance points for the months that they weren't employed.
I have attached a spreadsheet with data. As you can see -- Empl 2 in April has accrued points of .5 (the same as Empl 1) however, it should be 1.5 since Jan/Feb should be 0 for Perfect Attendance.
Note that the workbook that is provided to my managers has most of the cells protected. The days have drop down selections and Employee Name/Points Brought Forward/Start Date are the only cells that are unprotected.
Excellent. Works Perfect. Don't even need to do Conditional Formatting. Than You.