Forum Discussion
Excel Formulas
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.
Hi Sir,
Recently we have upgraded to office 365. After upgradation we are unable to use Erlang B formula npxcel32. Please provide your suggestion how to make to make it workable. Thank you.
Rony
Thanks for all your help. This is way beyond my knowledge level and I am getting blasted for continuing the conversation. I have a workaround that I can implement. I don't know how to close a conversation, but I won't be adding any more to this. Again thanks.
1. That is a simple VLOOKUP(). Did you never use a VLOOKUP() before? Take a look at the online help page.
2. When there is no employee then delete the row. Simple as that.
3. I was afraid this is getting more and more complex. With the current setup I will not be able to provide good solutions. Change your setup into a record list.
- This seems to be a perpetual conversation. I hereby admonish you to start a new one, given that you terminated this yourself.
OK. I see it seems to work however, I don't quite get this part: 'Employee Names'!$A$2:$C$3,3,FALSE)) ..
Can you break it down for me? I would think it should be $A$2:$C$2, for Employee 1 and
$A$3:$C$3, for Employee 2.... and what is the ,3, prior to the FALSE referring to?
Also, now there is a #N/A in the remaining cells for columns AI and AJ where there are no employees.
Not quite sure why my copy and paste didn't work....
=((COUNTA(C7:AG7)=0)*(B7<>0)*0,5)*(--(1&MonthName&CalendarYear)>=VLOOKUP([@[Employee Name]],'Employee Names'!$A$2:$C$3,3,FALSE))
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.
