Timekeeping for holidays can be a bit of a hassle if not done properly. To ensure accuracy and ease of control, here are some steps on how to timekeeping for holidays in Excel.
Nội dung
How to record holidays in Excel timesheet
Step 1: add a line to check if the day of the month is a holiday or a regular day
The formula used is
=IF(E4=””,0,IF(COUNTIF($B$15:$B$18,E4)=1,2,1))
Formula explanation:
IF(E4=””,0 means if the date cell in the month is empty, then the date type is 0 (no date)
IF(COUNTIF($B$15:$B$18,E4)=1 means that the if clause counts the value in E4 in the range B15:B18 (the range containing holidays), with a value of 1 (meaning that this date cell appears in the list of holidays)
The result is 2 => It is a holiday, the coefficient convention is 2
If it is not a holiday, the result is 1 => Normal day, the coefficient convention is 1
So we have 3 types of results:
- Result = 0 is no date
- Result = 1 is weekday
- Result = 2 is a holiday
Step 2: Build a formula to calculate the number of holidays
Continue the formula explanation section
=COUNTIFS($E$5:$AI$5,2,E7:AI7,””)
Here we use the COUNTIFS formula (with S, not COUNTIF, meaning counting by multiple conditions, specifically here there are 2 conditions)
- The range from E5 to AI5, provided the value = 2 (this range is fixed so it can remain the same when filling down the formula)
- Area from E7 to AI7, provided that the value is empty (no timekeeping symbol, this area is not fixed so that it can be changed when filling down for the next employees)
Step 3 : Count the number of working days on holidays
=COUNTIFS($E$5:$AI$5,2,E7:AI7,”<>”)
The formula used here is just a little different from going to work, which is “<>” which means non-empty (containing the holiday attendance symbol).
Holiday timekeeping in Excel helps manage working time and calculate salary accurately. Hopefully, this article has provided you with useful knowledge to effectively timekeeping holidays.
See more: Detailed instructions on how to use Filter data in Excel