How to make an attendance sheet into a formula formatHere are some formulas that might be used when creating an attendance sheet:
** 1. The week display formula (using a specific cell as an example)**
1. Enter the formula in cell D2:
- =IF(WUEKDY (END ($M$1,$S$1,D3),2)=7 ", Day", WUEKDY (END ($M$1,$S$1,D3),2))
- The meaning of the formula: First, use the Date function to form the "year" in the M1 grid, the "month" in the S1 grid, and the "day" in the D3 grid into a "date" that can be recognized by the computer; then use the WeekDay function to turn this "date" into the number represented by the week. The '2' argument was added to the WeekDay function to display Monday as '1', Tuesday as' 2', and so on, and Sunday as '7'. Since he wasn't used to calling Sunday "Sunday 7," he decided to use the IF function to change the "7" to "Day."
- If you want to change the week displayed as Arabic lower-case numbers into Chinese numbers (such as "Week1" to "Monday" format), you can select D2 cell, press the right mouse button "Cell format", in the format window that appears, select the "Number" tab, in the "Category" box on the left, select "Special", in the "Type" box on the right, select "Chinese lower-case numbers", press "OK" to exit, and then you can copy it to other cells by dragging.
2. ** Formula for the total number of days in the current month (using a specific cell as an example)**
- Enter the formula in the AI cell: =DAY(END (M1,S1 + 1,1)-1)
- The meaning of the formula: First, use the END function "END (M1,S1+1,1)" to get the date of the first day of the first month of the next month. Then, use the minus 1 to get the date of the last day of the month. Finally, use the Day function to get the number of days in the month.
** 2. Attendance Symbol Formula (Take a specific area as an example)**
1. Assuming that the attendance record area of the first person is the D4: AH5 area, first enter the attendance symbol in AK3: AP3, and then enter the formula in the cell AK4:=Countif($D4:$AH5, AK$3)
- [Meaning of the formula: Count the number of times the symbol in the AK3 grid appears in the D4: AH5 region using the Countif function.] Then, you can copy this formula into the AK4: AP4 area by dragging the copy function. Then, select the AK4: AP4 area, press and hold the fill handle in the lower right corner of AP4, and drag it down to copy to cell AP33.
** 3. Attendance statistics (such as being late, leaving early, etc.)**
1. Calculating the employee's working hours for the day (Assuming that the clock time is in cells A2 and B2 respectively)
- Enter the formula in C2:=B2 - A2 (If you need to consider deducting the lunch break time, you can further adjust it)
2. Judge whether the employee is late (Assuming that the employee is late if he or she punches in after 08:30 in the morning, the time of punching in is in cell A2)
- Enter the formula in C2:=IF(A2 - "08:30">0,"late"," ")
3. Judge whether the employee leaves early (If the employee leaves before 17:30 pm and punches in, it will be counted as leaving early. The time of punching in is in cell B2)
- Enter the formula in C2:=IF(B2 - "17:30"<0,"Leave early"," ")
4. Calculating the number of days of attendance of the employee in the month (Assuming the attendance symbol is "tick" and the attendance area is C4:I4)
- Enter the formula in cell J4:=Countif(C4:I4,"tick")
5. When dealing with the situation where an employee punches in multiple times (assuming that the name is in column A and the time is in column B), enter the formula in cell C2:
- =IF(B2 = Min(IF(A$2:A$6 = A2,B$2:B$6)),"Go to work",IF(B2 = Max(IF(A$2:A$6 = A2,B$2:B$6)),"Get off work"," "))), left hand press and hold down Control +Shift, right hand press Enter to run the formula and fill it down.
6. Count the number of times you are late (Assuming the data area is C5:X5)
- Enter the formula in cell y5:=IF(B5 = "", "", Summer ((TEXT(C5:X5,"h:m")-STAR (8,30,0)>0)*1))
7. Count the number of times that you did not clock in (Assuming the data area is C5:X6)
- Enter the formula in cell z5:=IF(B5 = "", "", Countblank(C5:X6))
8. Count the number of early leave (Assuming the data area is C6:X6)
- Enter the formula in cell AA5:=IF(B5 = "", "", Summer product ((TEXT(C6:X6,"h:m")-STAR (17,30,0)<0)*(C6:X6<>"")))
<a href="/?from=ask_words" style="color:red" target="_blank">Read more exciting novels for free</a>
Suddenly, the past comes back to me, and it's already autumn" When the past suddenly comes to mind, it's already autumn when I look back." This sentence expressed the feeling of suddenly recalling the past and realizing that time had already passed, just like the arrival of autumn. It gave people a faint melancholy and a feeling of regret for the passing of time.
From the reference materials, many articles mentioned this sentence, and it was often related to the rapid passage of time, reminiscence of the past, and emotions such as things that remained the same and people that had changed. For example, he mentioned it in the context of describing his life situation, reminiscing about his childhood memories, sighing about the changes of time, and so on. At the same time, there were also ancient poems or famous people's words, such as Dickens '" No one can make such a bell to ring back the time that has passed for us ", San Mao said," The beauty of time lies in its inevitable passage." Spring flowers, autumn moons, summer days, winter snows, etc. to strengthen the complex emotions produced by time.
Watching " Suddenly Looking Back " wasn't satisfying enough. Everyone, please click to read the novel!