How to create an Excel formula that deducts $10 every 10 minutes

How to create a formula in Excel to deduct 10 yuan within 10 minutes

First, design a table in Excel 2007, including basic conditions such as working hours, arrival time, lateness time, and salary deductions.

2. In cell D2 of working hours, fill in the working hours specified by most companies. The filling time is 08:00 in the morning.

Fill in the arrival time. In order to clearly show the effect, the time in many cells exceeds the specified time.

4. The formula for calculating lateness time is: =IF(HOUR(F2)

5. To deduct wages, enter the formula =LOOKUP(G2,{0,1,10},{0,10,10}) in the cell of G2. G2 is the cell where the calculation result is displayed, and then fill in the corresponding value in this {}, for example: 0 minutes late, 0 yuan will be deducted. 1 minute, 10 yuan deducted. 10 yuan is also deducted for 10 minutes. Then in the cell of calculation result G2, select the number 10 and pull down to fill in the following cells.

6. Adjust the row height in cell A1, then add the word [minutes] under the late time, set the font to Song Dynasty, and the font size to size 9. This will make the table look clear at a glance.

7. Select cells G2-21 where wages should be deducted, right-click the mouse [Set Customization], select [Currency as two-digit style as shown], and click [OK].

8. After setting the currency style, in the cell that displays the currency RMB 10, the filled font will be red and bold. Add a row at the bottom of the table, fill in a note, and fill it with orange. Finally, fill the border of the entire table with a light green color, which makes it look like spring. Green is refreshing and beautiful.

How to use functions to make an attendance sheet in excel to display late arrivals, early departures and missed clock-ins

1. First, open the check-in status table, click on the cell where the status needs to be displayed, and enter the statistical command. "=IF(D6="","not clocked in",IF(D6>TIME(8,0,0),"late",""))", where D6 represents the statistical time point, (8,0 ,0) represents 8:00.

2. After completing the input, click Enter and you will see the statistical status displayed in the cell. Place the mouse on the lower right corner of the cell, and when the mouse turns into a small cross, drag with the mouse to complete the attendance statistics for a column of time points.

3. After the statistics are completed, you can see the expected status, as shown in the figure below.

4. Early departure statistics

First, open the check-in status table, click on the cell where the status needs to be displayed, and enter the statistical command, "=IF(E6="","not clocked in",IF(E6

5. After completing the input, click Enter and you will see the statistical status displayed in the cell. Place the mouse on the lower right corner of the cell, and when the mouse turns into a small cross, drag with the mouse to complete the attendance statistics for a column of time points.

6. After the statistics are completed, you can see the expected status, as shown in the figure below.

The above is the detailed content of How to create an Excel formula that deducts $10 every 10 minutes. For more information, please follow other related articles on the PHP Chinese website!