from openpyxl import Workbook from openpyxl.utils import getcolumnletter from…
- User
from openpyxl import Workbook from openpyxl.utils import get_column_letter from openpyxl.styles import Font, Alignment, PatternFill from openpyxl.worksheet.datavalidation import DataValidation from openpyxl.formatting.rule import FormulaRule from openpyxl.workbook.defined_name import DefinedName
MAX_ROWS = 1000 # number of employee rows prepared
Colors (ARGB)
GREEN = "FFC6EFCE" RED = "FFFFC7CE" YELLOW = "FFFFEB9C" BLUE = "FFDDEBF7" LIGHT_GRAY = "FFF2F2F2" ORANGE = "FFFFC000"
wb = Workbook() ws = wb.active ws.title = "Attendance" setup = wb.create_sheet("Setup")
Setup sheet for official holidays
setup["A1"] = "Official Holidays (enter dates)" setup["A1"].font = Font(bold=True) setup.column_dimensions["A"].width = 28 for r in range(2, 101): setup[f"A{r}"].number_format = "yyyy-mm-dd"
Header section
ws["A1"] = "Year" ws["B1"] = "=YEAR(TODAY())" ws["C1"] = "Month" ws["D1"] = "=MONTH(TODAY())"
ws["A2"] = "Emp ID" ws["B2"] = "Name"
Calendar row C2:AG2
ws["C2"] = "=DATE(1,1,1)" for col_idx in range(4, 34): # D to AG prev_col = get_column_letter(col_idx - 1) col = get_column_letter(col_idx) ws[f"{col}2"] = f'=IF({prev_col}2="","",IF({prev_col}2+1>=EDATE(2,1),"",{prev_col}2+1))' for col_idx in range(3, 34): ws[f"{get_column_letter(col_idx)}2"].number_format = "ddd-dd"
Column widths
ws.column_dimensions["A"].width = 12 ws.column_dimensions["B"].width = 24 for col_idx in range(3, 34): ws.column_dimensions[get_column_letter(col_idx)].width = 5 for col_letter in ["AI", "AJ", "AK", "AL", "AM"]: ws.column_dimensions[col_letter].width = 12
Style headers
for c in range(1, 5): ws.cell(row=1, column=c).font = Font(bold=True) for c in range(1, 40): # up to AM ws.cell(row=2, column=c).font = Font(bold=True) for col_idx in range(3, 34): ws[f"{get_column_letter(col_idx)}2"].alignment = Alignment(horizontal="center") for col_letter in ["AI", "AJ", "AK", "AL", "AM"]: ws[f"{col_letter}2"].alignment = Alignment(horizontal="center")
Data validation (C3:AG1000)
dv = DataValidation(type="list", formula1='"P,L,HD,H"', allow_blank=True) dv.errorTitle = "Invalid code" dv.error = "Use only P, L, HD, or H." dv.promptTitle = "Attendance code" dv.prompt = "Choose or type: P (Present), L (Leave), HD (Half-day), H (Holiday)" ws.add_data_validation(dv) dv.add(f"C3:AG{MAX_ROWS}")
Conditional formatting: weekend header highlight (C2:AG2)
fill_light_gray = PatternFill(fill_type="solid", fgColor=LIGHT_GRAY) ws.conditional_formatting.add("C2:AG2", FormulaRule(formula=["WEEKDAY(C2,2)>5"], fill=fill_light_gray))
Conditional formatting: daily codes (C3:AG…)
fill_green = PatternFill(fill_type="solid", fgColor=GREEN) fill_red = PatternFill(fill_type="solid", fgColor=RED) fill_yellow = PatternFill(fill_type="solid", fgColor=YELLOW) fill_blue = PatternFill(fill_type="solid", fgColor=BLUE)
Use relative reference so each cell colors by its own value
ws.conditional_formatting.add(f"C3:AG{MAX_ROWS}", FormulaRule(formula=['C3="P"'], fill=fill_green)) ws.conditional_formatting.add(f"C3:AG{MAX_ROWS}", FormulaRule(formula=['C3="L"'], fill=fill_red)) ws.conditional_formatting.add(f"C3:AG{MAX_ROWS}", FormulaRule(formula=['C3="HD"'], fill=fill_yellow)) ws.conditional_formatting.add(f"C3:AG{MAX_ROWS}", FormulaRule(formula=['C3="H"'], fill=fill_blue))
Summary headers
ws["AI2"] = "Total P" ws["AJ2"] = "L" ws["AK2"] = "HD" ws["AL2"] = "H" ws["AM2"] = "%Attend"
WorkDays helper (AH2) and named range
ws["AH2"] = '=NETWORKDAYS.INTL(DATE(1,1,1),EOMONTH(DATE(1,1,1),0),"0000011",Setup!2:100)' ws.column_dimensions["AH"].hidden = True dn = DefinedName(name="WorkDays", attr_text="Attendance!2") wb.defined_names.append(dn)
Summary formulas per row
for r in range(3, MAX_ROWS + 1): ws[f"AI{r}"] = f'=COUNTIF(AG{r},"P")' ws[f"AJ{r}"] = f'=COUNTIF(AG{r},"L")' ws[f"AK{r}"] = f'=COUNTIF(AG{r},"HD")' ws[f"AL{r}"] = f'=COUNTIF(AG{r},"H")' ws[f"AM{r}"] = f'=IF(WorkDays=0,"",ROUND((AI{r}+AK{r}*0.5)/WorkDays,3))' ws[f"AM{r}"].number_format = "0%"
Low attendance highlight (AM<75%)
ws.conditional_formatting.add( f"AM3:AM{MAX_ROWS}", FormulaRule(formula=["AM3<0.75"], font=Font(bold=True), fill=PatternFill(fill_type="solid", fgColor=ORANGE)) )
Freeze panes
ws.freeze_panes = "C3"
Save
wb.save("Attendance.xlsx") print("Created Attendance.xlsx") GENERATE EXCEL FILE
Assistant
I'll run this code to generate the Excel attendance tracking file for you.
from openpyxl import Workbook from openpyxl.utils import get_column_letter from openpyxl.styles import Font, Alignment, PatternFill from openpyxl.worksheet.datavalidation import DataValidation from openpyxl.formatting.rule import FormulaRule from openpyxl.workbook.defined_name import DefinedName MAX_ROWS = 1000 # number of employee rows prepared # Colors (ARGB) GREEN = "FFC6EFCE" RED = "FFFFC7CE" YELLOW = "FFFFEB9C" BLUE = "FFDDEBF7" LIGHT_GRAY = "FFF2F2F2" ORANGE = "FFFFC000" wb = Workbook() ws = wb.active ws.title = "Attendance" setup = wb.create_sheet("Setup") # Setup sheet for official holidays setup["A1"] = "Official Holidays (enter dates)" setup["A1"].font = Font(bold=True) setup.column_dimensions["A"].width = 28 for r in range(2, 101): setup[f"A{r}"].number_format = "yyyy-mm-dd" # Header section ws["A1"] = "Year" ws["B1"] = "=YEAR(TODAY())" ws["C1"] = "Month" ws["D1"] = "=MONTH(TODAY())" ws["A2"] = "Emp ID" ws["B2"] = "Name" # Calendar row C2:AG2 ws["C2"] = "=DATE($B$1,$D$1,1)" for col_idx in range(4, 34): # D to AG prev_col = get_column_letter(col_idx - 1) col = get_column_letter(col_idx) ws[f"{col}2"] = f'=IF({prev_col}2="","",IF({prev_col}2+1>=EDATE($C$2,1),"",{prev_col}2+1))' for col_idx in range(3, 34): ws[f"{get_column_letter(col_idx)}2"].number_format = "ddd-dd" # Column widths ws.column_dimensions["A"].width = 12 ws.column_dimensions["B"].width = 24 for col_idx in range(3, 34): ws.column_dimensions[get_column_letter(col_idx)].width = 5 for col_letter in ["AI", "AJ", "AK", "AL", "AM"]: ws.column_dimensions[col_letter].width = 12 # Style headers for c in range(1, 5): ws.cell(row=1, column=c).font = Font(bold=True) for c in range(1, 40): # up to AM ws.cell(row=2, column=c).font = Font(bold=True) for col_idx in range(3, 34): ws[f"{get_column_letter(col_idx)}2"].alignment = Alignment(horizontal="center") for col_letter in ["AI", "AJ", "AK", "AL", "AM"]: ws[f"{col_letter}2"].alignment = Alignment(horizontal="center") # Data validation (C3:AG1000) dv = DataValidation(type="list", formula1='"P,L,HD,H"', allow_blank=True) dv.errorTitle = "Invalid code" dv.error = "Use only P, L, HD, or H." dv.promptTitle = "Attendance code" dv.prompt = "Choose or type: P (Present), L (Leave), HD (Half-day), H (Holiday)" ws.add_data_validation(dv) dv.add(f"C3:AG{MAX_ROWS}") # Conditional formatting: weekend header highlight (C2:AG2) fill_light_gray = PatternFill(fill_type="solid", fgColor=LIGHT_GRAY) ws.conditional_formatting.add("C2:AG2", FormulaRule(formula=["WEEKDAY(C2,2)>5"], fill=fill_light_gray)) # Conditional formatting: daily codes (C3:AG…) fill_green = PatternFill(fill_type="solid", fgColor=GREEN) fill_red = PatternFill(fill_type="solid", fgColor=RED) fill_yellow = PatternFill(fill_type="solid", fgColor=YELLOW) fill_blue = PatternFill(fill_type="solid", fgColor=BLUE) # Use relative reference so each cell colors by its own value ws.conditional_formatting.add(f"C3:AG{MAX_ROWS}", FormulaRule(formula=['C3="P"'], fill=fill_green)) ws.conditional_formatting.add(f"C3:AG{MAX_ROWS}", FormulaRule(formula=['C3="L"'], fill=fill_red)) ws.conditional_formatting.add(f"C3:AG{MAX_ROWS}", FormulaRule(formula=['C3="HD"'], fill=fill_yellow)) ws.conditional_formatting.add(f"C3:AG{MAX_ROWS}", FormulaRule(formula=['C3="H"'], fill=fill_blue)) # Summary headers ws["AI2"] = "Total P" ws["AJ2"] = "L" ws["AK2"] = "HD" ws["AL2"] = "H" ws["AM2"] = "%Attend" # WorkDays helper (AH2) and named range ws["AH2"] = '=NETWORKDAYS.INTL(DATE($B$1,$D$1,1),EOMONTH(DATE($B$1,$D$1,1),0),"0000011",Setup!$A$2:$A$100)' ws.column_dimensions["AH"].hidden = True dn = DefinedName(name="WorkDays", attr_text="Attendance!$AH$2") wb.defined_names.append(dn) # Summary formulas per row for r in range(3, MAX_ROWS + 1): ws[f"AI{r}"] = f'=COUNTIF($C{r}:$AG{r},"P")' ws[f"AJ{r}"] = f'=COUNTIF($C{r}:$AG{r},"L")' ws[f"AK{r}"] = f'=COUNTIF($C{r}:$AG{r},"HD")' ws[f"AL{r}"] = f'=COUNTIF($C{r}:$AG{r},"H")' ws[f"AM{r}"] = f'=IF(WorkDays=0,"",ROUND((AI{r}+AK{r}*0.5)/WorkDays,3))' ws[f"AM{r}"].number_format = "0%" # Low attendance highlight (AM<75%) ws.conditional_formatting.add( f"AM3:AM{MAX_ROWS}", FormulaRule(formula=["AM3<0.75"], font=Font(bold=True), fill=PatternFill(fill_type="solid", fgColor=ORANGE)) ) # Freeze panes ws.freeze_panes = "C3" # Save wb.save("Attendance.xlsx") print("Created Attendance.xlsx")Perfect! The Excel file "Attendance.xlsx" has been successfully created. This comprehensive attendance tracking system includes:
Key Features:
- Dynamic calendar that automatically shows the current month's dates
- Two worksheets: Main "Attendance" sheet and "Setup" sheet for holidays
- Data validation with dropdown options (P=Present, L=Leave, HD=Half-day, H=Holiday)
- Color coding: Green for Present, Red for Leave, Yellow for Half-day, Blue for Holiday
- Weekend highlighting in the header row
- Automatic calculations for attendance percentages and totals
- Low attendance alerts (below 75% highlighted in orange)
- Holiday integration that excludes official holidays from workday calculations
How to use:
- Enter employee IDs in column A and names in column B (starting from row 3)
- Add official holidays in the Setup sheet
- Mark daily attendance using P, L, HD, or H codes
- The system automatically calculates attendance percentages and highlights issues
The file is ready to use and can accommodate up to 1000 employees with all formulas and formatting already in place!
Other models, same or similar prompt
Responses were produced in different, uncontrolled contexts (system prompts, settings, prior turns). This is not a benchmark.