WHAT FUNCTION SHOULD EXCEL USE TO CALCULATE COMMUTING ATTENDANCE?

1.First, take a look at the raw data. At a minimum, the attendance record contains at least the name (or employee number), date and time information.

Actual situations include missing clock-in, that is, a person only clocks in once a day; Repeat clock-in, that is, more than two clock-in records in a day. Below, identify these situations through actions.

2.First, select the original data area and select [Insert] - [Pivot Table] in the ribbon.

3.Follow the default settings and click the OK button to create a pivot table in the new table.

4.After creating a pivot table, the effect is as follows: the worksheet interface is an empty pivot table, and on the right is the drag area of each field of the pivot table, row labels, numeric values and other fields.

5.Drag the Name field to the Row Label, and drag the Swipe Time to the Numeric box.

6.Click the Swipe Time field and select Value Field Settings.

7.As shown in the following figure, change the name to "Working Hours", and select [Minimum] below.

8.In the same way, drag the [Swipe Time] field to the [Value] box again, set the field, change the name to "After-hours time", and select [Maximum] below.

9.Select the numeric area in the pivot table, right-click and select Format Cells, and select a time format.

10.In this way, a statistical table is generated with the names of people and the time of commuting.

If the clock-in time and clock-out time are the same, the employee missed the clock-in, as recorded in the red box in the following figure.

Identify if an employee is late by judging if the start time is before the stated start time.

Identify whether an employee leaves early by judging whether the clock-off time is after the allotted clock-off time.

Microsoft Excel is one of the components of Microsoft Office, Microsoft's office software, which is a spreadsheet software written and run by Microsoft for computers with Windows and Apple Macintosh operating systems, and the earliest version was developed in 1985.

Microsoft Excel can carry out various data processing, statistical analysis and auxiliary decision-making operations, etc., with functions such as opening or closing header rows, adjusting each row with a single formula for calculated columns, data sorting and filtering, custom formulas and text input. At present, Microsoft Excel is widely used in many fields such as management, statistics, finance, and finance.

Read it line by line, and don't look at the results first.

The sender told me.

It takes about 1-2 minutes, but it feels like it's worth it.

Give it a try. First, prepare paper and pen.

When choosing a name, choose the real name of someone you actually know.

Write down the answers that come to mind in an instant, based on your intuition.

Read one line at a time.

If you read the answer first, it won't be interesting.

1。Start by writing the numbers from 1 to 10 from top to bottom (vertical).

2。Next to the numbers 1 and 2 write a number (a single digit between 1 and 10) 3. Secondly, write the name of a person of the opposite sex next to the numbers 3 and 7.

4。Write a name next to the numbers 4, 5, and 6. Friends, family and family are fine.

If you are not serious, it is equivalent to wasting your own time)

5。Next to the numbers 8, 9, and 10 write the title of a song...

By first feeling).

6。Finally, make a wish ba....

Recently, I want to happen the most wish, a miracle or something.

With a wish, let's see the outcome of this game.

First of all, next to the number 3, is the person you love.

Write next to the number 7 the love you like, but can't achieve.

The person next to the number 4 is the one who cares about you the most.

The person next to the number 5 is the one who brings you good luck.

The person next to the number 6 is the one who is not good for you.

The song written in the number 8 is the most suitable song for the person who writes the number 3.

The song written in the number 9 is the most suitable song for the person who writes the number 7.

The songs written down in the number 10 are the songs that are best suited to express your thoughts.

Generally, use the if and sum functions a little more.

Excel: How long does it take for attendance data to come out? I'll teach you how to do it in 5 seconds.

It's a bit complicated, and I don't know how to use Excel.

1. First of all, make sure that the cell that enters the time is in time format, that is, h:mm2, and then calculate the cell of the hour input formula =hour(b2-a2)+hour(d2-c2), and sum the hours in the morning and afternoon.

3. Enter the formula =minute(b2-a2)+minute(d2-c2) in the cell to calculate the minutes, and add up the minutes in the morning and afternoon.

Reference. How to quickly convert attendance records into three lists in batches.

How to quickly delete the duplicate time of the attendance record sheet in batches.

1. First of all, record everyone's name and corresponding clock-in time in Excel.

2. Then click the "Data" tab in the menu bar, and find the "Column" in the "Data Tools" we need in the Data tab.

3. Click "Column", pop up the tab, select "Fixed Width", and then click the "Next" button.

4. In the text sorting wizard, adjust the position of the list appropriately so that each time point is separated, and then click the "Next" button.

5. The list is divided separately.

6. According to the above steps, on the corresponding side of the column "overtime", as shown in the figure, enter the function "=text(mod(c2-b2,1),"."h hours m minutes"Then click the enter key and the D2 content will be displayed.

7. When the cross-shaped cursor appears below the display result bar, pull down or double-click to fill in all the content.

Nowadays, many companies implement a clock-in/out system. The clock-in time is used as the basis for whether there is a late arrival or overtime. Next, I will talk about how to calculate overtime hours through attendance using Excel software.

03 The overtime hours of the employee will come out, and the overtime hours of the remaining employees will be automatically calculated and generated by dropping down the cells to fill in the format.

04 In this process, we mainly applied two functions: the text function (the value is converted to text) and the mod function. Find the remainder.

1. Introduction to common functions and formulas of timesheets:

First of all, the most basic thing is to calculate the time of the employee to work that day, as shown in the figure below, enter the formula =b2-a2 in C2 to get the length of the employee's work on the day (you can consider subtracting the time of the lunch break).

2. In addition, judging whether employees are late or leaving early is also a basic function of the attendance sheet. Assuming that clocking in after 08:30 in the morning is considered late, then c2=if(a2-'08:30'>0,'Arriving late','Can.

3. Assuming that leaving before 17:30 in the evening is considered to be an early departure, then c2=if(b2-'17:30'<0,'Leave early','Can.

4. As shown in the figure below, employees should mark the check mark under the date when they go to work normally, otherwise they will be marked with a circle (the actual situation may be more complicated, such as sick leave, personal leave, paid annual leave, etc.). The number of days an employee attends in the month can be calculated using the formula: j4=countif(c4:i4,'√

5. If the system swipes the card or punches in by fingerprint, the generated data may be the following **, one column is the name, and the other column is the punch-in time of each time. Since a person may clock in and out more than once a day, and may also miss clocking in and out of work, it is also necessary to identify it. So let's create a label auxiliary column in column C.

6. Double-click C2 and enter the formula: =IF(B2=Min(IF(A$2:A$6=A2,B$2:

b$6)), to work', if(b2=max(if(a$2:a$6=a2,b$2:b$6)), clock-off','Hold down Ctrl Shift with your left hand and press Enter with your right hand to run the formula and fill in the formula down.

7. min(if(a$2:a$6=a2,b$2:b$6)) returns the minimum value of "Zhang San" on the day; max(if(a$2:

a$6=a2,b$2:b$6)) What is returned is the maximum value of "Zhang San" clocked in on the day; The effect of nesting if outside is: if the time of column B is equal to the minimum value of the clock-in of "Zhang San" on the day, the work will be recorded; If the time in column b is equal to the maximum value of "Zhang San" clocked in on the day, the clock-out will be recorded; The punch record in the middle is empty.

8. Select the data region, click [Insert] - [Pivot Table], and create it according to the default settings.

10. The time under the "clock-in" and "off-duty" fields is the earliest and latest clock-in record of the employee on the day respectively, if the "spike off-duty" record is empty, it is only one card on the day (because only one record is counted in the "work" time).

First, use if and weekday to determine whether it is a weekend, and then use roundup and rounddown to take the half-hour interval value of the commuting time.

The formula needs three conditions, the date is in column A, and the time of work and time in and out of work is in column BC.

Formula: =if(weekday(a1,2)>5,rounddown(c1*24*2,0) 2-roundup(b1*24*2,0) 2,""

<> enter or copy and paste the following formula in d2.

if(weekday(a2,2)>5,floor(c2,1/24/2)-ceiling(b2,1/24/2),"

Pull-down padding. The formula only takes into account weekends and does not take into account non-weekends.

Working hours = ceiling(a1,--0:30"Clock-off time = floor(b1,--0:30"Let's assume that the start time is A1 and the end time is B1

with a custom function.

Excel often needs to use attendance formulas for attendance, how to use attendance formulas? The following is a tutorial on the use of excel** attendance formula, I hope it will inspire you after reading!

Excel** Attendance Formula Tutorial:

Step 1 of the attendance formula: First of all, the most basic thing is to calculate the time of the employee going to work on the same day, as shown in the figure below, enter the formula =b2-a2 in C2 to get the employee's working time on the day (you can consider subtracting the time of the noon break).

Attendance formula use step 2: In addition, judging whether an employee is late or leaving early is also a basic function of the timesheet.

Assuming that clocking in after 08:30 in the morning is considered late, then c2=if(a2-"08:30">0,"Arriving late","Can.

Step 3 of the attendance formula: If you leave before 17:30 in the evening to clock in and out, you will be considered to leave early, then c2=if(b2-"17:30"<0,"Leave early","Can.

Step 4 of the attendance formula: As shown in the figure below, if the employee goes to work normally, the check mark is marked under the date, otherwise the circle is marked (the actual situation may be more complicated, such as sick leave, personal leave, paid annual leave, etc.). Calculating the number of days an employee is present in the month can be calculated using the formula:

j4=countif(c4:i4,"√

Step 5 of the attendance formula: If the system swipes the card or punches in by fingerprint, the generated data may be the following formula, one column is the name, and the other column is the clock-in time of each time. Since a person may clock in and out multiple times a day, and it is also possible to miss clocking in and out of work, it is also necessary to identify them.

So let's create a label auxiliary column in column C.

Step 6: Double-click C2 and enter the formula: =if(b2=min(if(a$2:

a$6=a2,b$2:b$6)), to work",if(b2=max(if(a$2:a$6=a2,b$2:

b$6)), off work","Hold down Ctrl Shift with your left hand and press Enter with your right hand to run the formula and fill in the formula down.

The attendance formula uses step 7: min (if(a$2:a$6=a2,b$2:b$6)) to return the minimum value of Zhang San's attendance on the day.

max(if(a$2:a$6=a2,b$2:b$6)) returns the maximum value of Zhang San's punch in on the day;

The outer nested if to achieve the effect is:

If the time in column b is equal to the minimum value of "Zhang San" clocking in on the day, "go to work" will be recorded;

If the time in column b is equal to the maximum value of "Zhang San" clocked in on the day, the clock-out will be recorded;

The punch record in the middle is empty.

Step 8: Select the data region, click [Insert] - [Pivot Table], and create it according to the default settings.

Step 10 of the attendance formula: The time under the "Clock-in" and "Clock-out" fields is the earliest and latest clock-in record of the day for the employee, and if the "Clock-out" record is empty, the card is only played once that day (because only one record is counted in the "clock-in" time).