Fully Automated Attendance Sheet बनाने के Steps
Step 1: Employee Details तैयार करें
नीचे की तरफ Month और Year की लिस्ट तैयार करें।
ऊपर की तरफ Month और Year के लिए अलग-अलग सेल रखें।
Step 2: Month की Dropdown List बनाएं
Month वाले सेल को Select करें।
Data → Data Validation में जाएं।
Allow में List चुनें।
Source में नीचे बनाई गई Months की List Select करें।
OK करें।
अब Month की Dropdown List तैयार हो जाएगी।
Step 3: Year की Dropdown List बनाएं
Year वाले सेल को Select करें।
Data → Data Validation में जाएं।
Allow → List चुनें।
Source में बनाई गई Years की List Select करें।
OK करें।
अब Year भी Dropdown से Select किया जा सकता है।
Step 4: Month की Starting Date निकालें
Starting Date वाले सेल में DATEVALUE Formula का उपयोग करें।
Month और Year को Formula में जोड़ें।
उदाहरण:
=DATEVALUE("01"&MonthCell&YearCell)
Result को Short Date Format में बदलें।
जरूरत के अनुसार Column Width बढ़ाएं।
Step 5: Month की Ending Date निकालें
End Date वाले सेल में EOMONTH Formula लगाएं।
उदाहरण:
=EOMONTH(StartingDateCell,0)
Result को Short Date Format में बदलें।
अब चुने गए Month की Starting और Ending Date दोनों मिल जाएंगी।
Step 6: Dates Automatically Generate करें
पहली Date वाले सेल में Starting Date को Reference करें।
Date को केवल Number के रूप में दिखाने के लिए:
Format Cells → Custom में जाएं।
Date Format में केवल dd रखें।
अगली Date के लिए IF Formula का उपयोग करें।
Formula में पिछली Date में +1 करें और End Date तक Dates Generate करें।
Formula को नीचे Drag करें।
इस तरह पूरे Month की Dates Automatically Generate हो जाएंगी।
Step 7: Days के नाम निकालें
Date के अनुसार Day का नाम दिखाने के लिए TEXT Formula का उपयोग करें।
उदाहरण:
=TEXT(DateCell,"ddd")
इससे Monday, Tuesday, Wednesday आदि दिखाई देंगे।
Formula को पूरे Date Range में Drag करें।
Step 8: Days को Rotate करें
Days वाले पूरे Area को Select करें।
Home → Alignment → Orientation में जाएं।
Rotate Text Select करें।
इससे Sunday, Monday आदि Vertical दिखाई देंगे।
जरूरत के अनुसार Cells को Merge और Format करें।
Step 9: Non-Blank Cells को Highlight करें
पूरे Attendance Area को Select करें।
Conditional Formatting → New Rule में जाएं।
Format Only Cells That Contain चुनें।
Condition रखें कि Cell Not Blank हो।
Font को Bold करें।
Text Color को White रखें।
अपनी पसंद का Fill Color दें।
OK करें।
Step 10: Non-Blank Cells पर Border लगाएं
Attendance Area Select करें।
Conditional Formatting → New Rule में जाएं।
Use a Formula to Determine Which Cells to Format चुनें।
Formula में Blank न होने की Condition लगाएं।
Format → Border → Outside Border चुनें।
अब केवल Filled Cells पर Border दिखाई देगा।
Step 11: Sunday को Red Color में Highlight करें
पूरे Attendance Area को Select करें।
Conditional Formatting → New Rule में जाएं।
Formula में Day को "Sunday" के बराबर Check करें।
Format में Red Fill Color चुनें।
OK करें।
अब सभी Sundays Automatically Red Color में Highlight होंगे।
Step 12: Summary Fields बनाएं
ऊपर या Side में ये Fields बनाएं:
Total Days
Total Present
Total Absent
Week Off
Holiday
Percentage
इन Fields को Merge, Center, Bold और उचित Color से Format करें।
Step 13: Present और Holiday को Highlight करें
Attendance Area Select करें।
Conditional Formatting → Highlight Cells Rules → Text that Contains में जाएं।
अगर Cell में H लिखा हो तो उसे Light Green Color दें।
अगर Cell में A लिखा हो तो उसे Light Red Color दें।
इससे Present/Absent/Holiday की Entries आसानी से दिखाई देंगी।
Step 14: Sunday पर Attendance Entry रोकें
पूरे Attendance Area को Select करें।
Data → Data Validation में जाएं।
Allow → Custom चुनें।
Formula में Sunday को Check करें।
Sunday होने पर Entry को Allow न करने की Setting करें।
Error Alert में:
Title: Weekly Off
Message: Attendance Not Allowed Sunday
अब Sunday वाले दिन Attendance Enter करने पर Error Message आएगा।
Step 15: Attendance Sheet का Automatic Title बनाएं
ऊपर वाले Header Area को Select करें।
Merge & Center करें।
Formula से Title बनाएं जिसमें:
Fully Automatic Attendance Sheet
Selected Month
Selected Year
शामिल हों।
उदाहरण:
="Fully Automatic Attendance Sheet "&MonthCell&" "&YearCell
Text को Bold, White और Center में Format करें।
Background में अपनी पसंद का Color लगाएं।
Step 16: Total Days निकालें
Total Days वाले Cell में COUNTA Formula का उपयोग करें।
पूरे Date Range को Select करें।
Absolute Reference के लिए F4 का उपयोग करें।
अब चुने गए Month के अनुसार Total Days Automatically बदलेंगे।
Step 17: Total Present निकालें
Total Present वाले Cell में COUNTIF Formula लगाएं।
Attendance Range को Select करें।
Criteria में "P" लिखें।
उदाहरण:
=COUNTIF(AttendanceRange,"P")
Step 18: Total Absent निकालें
Total Absent के लिए उसी COUNTIF Formula का उपयोग करें।
Criteria में "A" रखें।
उदाहरण:
=COUNTIF(AttendanceRange,"A")
Step 19: Holiday Count निकालें
Holiday के लिए COUNTIF Formula लगाएं।
Criteria में "H" रखें।
उदाहरण:
=COUNTIF(AttendanceRange,"H")
Step 20: Week Off Count निकालें
Week Off के लिए COUNTIF Formula लगाएं।
Day वाले Range को Select करें।
Criteria में "Sunday" रखें।
इससे पूरे Month में कितने Sundays हैं, यह Automatically Count हो जाएगा।
Step 21: Attendance Fill करें
Attendance Cells में:
P = Present
A = Absent
H = Holiday
Sunday वाले Cells में कोई Attendance Entry नहीं डाली जा सकेगी।
जैसे-जैसे Attendance Fill करेंगे, Present, Absent, Holiday और Week Off की Summary Automatically Update होगी।
Step 22: Final Formatting करें
पूरी Attendance Table को Select करें।
All Borders Apply करें।
सभी Entries को Center Align करें।
जरूरत के अनुसार Font Size बढ़ाएं।
Column Width को AutoFit करें।
Header को Bold और Highlight करें।
Step 23: Final Result
अब आपकी Fully Automated Attendance Sheet तैयार है।
इसमें:
Month बदलने पर Dates Automatically बदलेंगी।
Year बदलने पर Dates Automatically बदलेंगी।
Days Automatically Generate होंगे।
Sundays Automatically Highlight होंगे।
Sunday पर Attendance Entry नहीं हो सकेगी।
Present, Absent और Holiday Automatically Count होंगे।
Total Days Automatically Calculate होंगे।
Attendance भरने के साथ Summary Automatically Update होगी।
OR
Steps to Create a Fully Automated Attendance Sheet
Step 1: Prepare Employee Details
In Sheet 2, enter all Employee IDs and Employee Names.
Create a list of Months and Years at the bottom.
Keep separate cells at the top for selecting the Month and Year.
Step 2: Create a Month Dropdown List
Select the cell where you want to select the Month.
Go to Data → Data Validation.
Under Allow, select List.
In the Source field, select the list of Months created below.
Click OK.
Your Month dropdown list is now ready.
Step 3: Create a Year Dropdown List
Select the Year cell.
Go to Data → Data Validation.
Select Allow → List.
In the Source field, select the list of Years.
Click OK.
Your Year dropdown list is now ready.
Step 4: Calculate the Starting Date of the Month
In the Starting Date cell, use the DATEVALUE formula.
Combine the selected Month and Year in the formula.
Example:
=DATEVALUE("01"&MonthCell&YearCell)
Change the result to Short Date format.
Increase the column width if required.
Step 5: Calculate the Ending Date of the Month
Use the EOMONTH formula in the End Date cell.
Example:
=EOMONTH(StartingDateCell,0)
Change the result to Short Date format.
Now you will have both the Starting Date and Ending Date of the selected Month.
Step 6: Generate Dates Automatically
In the first Date cell, reference the Starting Date.
To display only the day number:
Go to Format Cells → Custom.
Use dd as the date format.
For the next date, use an IF formula.
Add +1 to the previous date and continue until the End Date.
Drag the formula across the required range.
The dates for the entire month will now be generated automatically.
Step 7: Generate Day Names
Use the TEXT formula to display the Day name according to each date.
Example:
=TEXT(DateCell,"ddd")
This will display Monday, Tuesday, Wednesday, etc.
Drag the formula across the entire date range.
Step 8: Rotate the Day Names
Select the entire Day-name area.
Go to Home → Alignment → Orientation.
Select Rotate Text.
The Day names will now appear vertically.
Merge and format the cells as required.
Step 9: Highlight Non-Blank Cells
Select the entire Attendance Area.
Go to Conditional Formatting → New Rule.
Select Format Only Cells That Contain.
Set the condition to check if the cell is Not Blank.
Make the font Bold.
Set the text color to White.
Choose your preferred Fill Color.
Click OK.
Step 10: Add Borders to Non-Blank Cells
Select the Attendance Area.
Go to Conditional Formatting → New Rule.
Select Use a Formula to Determine Which Cells to Format.
Create a condition to check that the cell is not blank.
Go to Format → Border → Outside Border.
Now only filled cells will display the border.
Step 11: Highlight Sundays in Red
Select the entire Attendance Area.
Go to Conditional Formatting → New Rule.
Create a formula that checks whether the Day is "Sunday".
Set the Fill Color to Red.
Click OK.
All Sundays will now be automatically highlighted in red.
Step 12: Create Summary Fields
Create the following fields above or beside the Attendance Sheet:
Total Days
Total Present
Total Absent
Week Off
Holiday
Percentage
Merge, center, bold, and format these fields as required.
Step 13: Highlight Attendance Entries
Select the Attendance Area.
Go to Conditional Formatting → Highlight Cells Rules → Text that Contains.
If the cell contains H, apply a Light Green color.
If the cell contains A, apply a Light Red color.
This will make the attendance entries easy to identify.
Step 14: Prevent Attendance Entry on Sundays
Select the entire Attendance Area.
Go to Data → Data Validation.
Select Allow → Custom.
Create a formula that checks whether the selected day is Sunday.
Set the validation so that attendance is not allowed on Sundays.
In the Error Alert, enter:
Title: Weekly Off
Message: Attendance Not Allowed Sunday
Now, if someone tries to enter attendance on a Sunday, an error message will appear.
Step 15: Create an Automatic Attendance Sheet Title
Select the Header Area.
Use Merge & Center.
Create a formula that combines:
Fully Automatic Attendance Sheet
Selected Month
Selected Year
Example:
="Fully Automatic Attendance Sheet "&MonthCell&" "&YearCell
Make the text Bold, White, and Center Aligned.
Apply your preferred background color.
Step 16: Calculate Total Days
In the Total Days cell, use the COUNTA formula.
Select the complete Date Range.
Use F4 to make the reference absolute.
Total Days will automatically change according to the selected Month.
Step 17: Calculate Total Present
Use the COUNTIF formula in the Total Present cell.
Select the Attendance Range.
Use "P" as the criteria.
Example:
=COUNTIF(AttendanceRange,"P")
Step 18: Calculate Total Absent
Use the same COUNTIF formula for Total Absent.
Use "A" as the criteria.
Example:
=COUNTIF(AttendanceRange,"A")
Step 19: Calculate Total Holidays
Use the COUNTIF formula for Holidays.
Use "H" as the criteria.
Example:
=COUNTIF(AttendanceRange,"H")
Step 20: Calculate Week Offs
Use the COUNTIF formula for Week Off.
Select the Day-name range.
Use "Sunday" as the criteria.
This will automatically count the number of Sundays in the selected Month.
Step 21: Enter Attendance
Use the following codes in the Attendance cells:
P = Present
A = Absent
H = Holiday
Attendance cannot be entered on Sundays.
As you enter attendance, the Present, Absent, Holiday, and Week Off counts will automatically update.
Step 22: Apply Final Formatting
Select the entire Attendance Table.
Apply All Borders.
Center Align all entries.
Increase the Font Size if required.
Use AutoFit Column Width.
Make the Header Bold and Highlighted.
Step 23: Final Result
Your Fully Automated Attendance Sheet is now ready.
With this sheet:
Changing the Month automatically changes the Dates.
Changing the Year automatically updates the Dates.
Day names are generated automatically.
Sundays are automatically highlighted.
Attendance cannot be entered on Sundays.
Present, Absent, and Holiday are automatically counted.
Total Days are automatically calculated.
The Summary automatically updates as you enter attendance.