Visual Basic / EXCEL expert needed
Budget: $50 – $0 USD
Unpaid wages need to be calculated based on 29 C.F.R. 531.56(f)(4). There are two ways that non-tipped time is made to be paid at minimum wage, the first is for any shift where a server spent more than 30 minutes without customers, that entire period without customers should have been paid at the full minimum wage.
Below are three examples.
The second is that for any week where the server spent more than 20% of her time performing non-tipped work, that period should be paid at the full minimum wage.
For this calculation, please assume that 1 hour per shift was spent performing non-tipped work while serving customers in addition to the amount of time the server spent working without customers.
For example, if the total hours worked in a week by a server is 30 hours and the server spent 5 hours (5 shifts times 1 hour per shift) performing tipped work while serving customers and 6 hours in the week without customers, then there is a total of 11 hours performing non-tipped work. This means that 36.67% of the time spent by the server performing work in the week was on non-tipped work. That 11 hours should be paid at the full minimum wage.
Approach
To calculate the difference between the BWW payments and what was legally required the actual time at tables must be calculated.
To do this the use of a programming language is needed, in this case, VBA would be the most straightforward as it is within Excel.
The purpose of the program will be to automatically apply the law as stated above to the given data to calculate the true deserved pay. The first step is to calculate the actual time a server was with tables this can be done in the same manner for every day and employee, so it should be nested within a loop that cycles through all valid shifts to find how much time an employee spent with tables each shift. To do this comparison should be used to find each period that an employee was with customers, the comparison would be between first the start time of a table and then between the start and end and then between the end and end, with the length of time between the start to the end of the last table that was within that unbroken stretch added to the measurement of time with tables, this will be done for all the tables and the total will be documented and saved for future calculations. In this calculation, it is important to filter out entries that are food delivery orders that they entered into the system.
This tipped time will be subtracted from the time they were clocked in and if that difference is greater than 30 minutes all of the non-tipped time will be paid at minimum wage, state or federal whichever is higher, subtracted by the tipped wage paid by the restaurant.
The second is to work with the whole week to find if the server worked more than 20% of the time non-tipped then all of the non-tipped time that week would be paid at minimum wage. To do this the values calculated above will be split into weeks and the tipped time will be subtracted by 1 hour to account for non-tipped work done while serving tables. on will be to factor in the 1 hour per shift spent on non-tipped work while they had tables, this is done by subtracting 1 hour from the tipped time calculated above. After this is done the new tipped time is subtracted from the clocked-in time giving the new measurement of tipped versus non-tipped work that day. Each day of the week is compared and the percentage of time spent doing non-tipped work is found and if the percentage is greater than 20% all non-tipped time is paid at minimum wage minus the tipped wage.
Below are three examples.
The second is that for any week where the server spent more than 20% of her time performing non-tipped work, that period should be paid at the full minimum wage.
For this calculation, please assume that 1 hour per shift was spent performing non-tipped work while serving customers in addition to the amount of time the server spent working without customers.
For example, if the total hours worked in a week by a server is 30 hours and the server spent 5 hours (5 shifts times 1 hour per shift) performing tipped work while serving customers and 6 hours in the week without customers, then there is a total of 11 hours performing non-tipped work. This means that 36.67% of the time spent by the server performing work in the week was on non-tipped work. That 11 hours should be paid at the full minimum wage.
Approach
To calculate the difference between the BWW payments and what was legally required the actual time at tables must be calculated.
To do this the use of a programming language is needed, in this case, VBA would be the most straightforward as it is within Excel.
The purpose of the program will be to automatically apply the law as stated above to the given data to calculate the true deserved pay. The first step is to calculate the actual time a server was with tables this can be done in the same manner for every day and employee, so it should be nested within a loop that cycles through all valid shifts to find how much time an employee spent with tables each shift. To do this comparison should be used to find each period that an employee was with customers, the comparison would be between first the start time of a table and then between the start and end and then between the end and end, with the length of time between the start to the end of the last table that was within that unbroken stretch added to the measurement of time with tables, this will be done for all the tables and the total will be documented and saved for future calculations. In this calculation, it is important to filter out entries that are food delivery orders that they entered into the system.
This tipped time will be subtracted from the time they were clocked in and if that difference is greater than 30 minutes all of the non-tipped time will be paid at minimum wage, state or federal whichever is higher, subtracted by the tipped wage paid by the restaurant.
The second is to work with the whole week to find if the server worked more than 20% of the time non-tipped then all of the non-tipped time that week would be paid at minimum wage. To do this the values calculated above will be split into weeks and the tipped time will be subtracted by 1 hour to account for non-tipped work done while serving tables. on will be to factor in the 1 hour per shift spent on non-tipped work while they had tables, this is done by subtracting 1 hour from the tipped time calculated above. After this is done the new tipped time is subtracted from the clocked-in time giving the new measurement of tipped versus non-tipped work that day. Each day of the week is compared and the percentage of time spent doing non-tipped work is found and if the percentage is greater than 20% all non-tipped time is paid at minimum wage minus the tipped wage.