About Prompt
- Prompt Type – Dynamic
- Prompt Platform – ChatGPT
- Niche – HR
- Language – English
- Category – Productivity
- Prompt Title – ChatGPT Prompt for Generating Excel Attendance Tracker Templates
Prompt Details
—
### **Optimized Dynamic Prompt: Excel HR Attendance Tracker Generator**
**Prompt to be provided to ChatGPT:**
**Act as an expert HR Systems Analyst and a master of Microsoft Excel, specializing in creating automated, user-friendly, and scalable productivity tools. Your task is to generate a comprehensive and professional Excel Attendance Tracker template based on the dynamic inputs provided below.**
The goal is to create a template that minimizes manual data entry, provides clear visual insights, and is easy for an HR manager or team lead to use and maintain throughout a specified period.
—
### **PART 1: DYNAMIC INPUTS (Fill in the details below)**
* **[1. Company Name]:** Your Company Name (e.g., “Innovate Corp”)
* **[2. Tracking Period]:** Specify the year and month (e.g., “2024,” “July 2024,” or “Q3 2024”).
* **[3. Employee List]:** Provide a comma-separated list of employee names and optional IDs. (e.g., “E001 – John Doe, E002 – Jane Smith, E003 – Peter Jones”). If the list is long, state the number of employees (e.g., “25 employees”).
* **[4. Attendance Codes]:** Define the codes to be used and their meanings. (e.g., “P: Present, A: Absent (Unplanned), SL: Sick Leave, V: Vacation, H: Holiday, WFH: Work From Home, T: Training”).
* **[5. Work Week]:** Specify the standard working days. (e.g., “Monday to Friday”).
* **[6. Holiday List]:** Provide a comma-separated list of holiday dates within the tracking period. (e.g., “2024-01-01, 2024-07-04, 2024-12-25”).
* **[7. Key Features Required]:**
* **Automated Dashboard:** (Yes/No)
* **Conditional Formatting:** (Yes/No – for color-coding attendance codes)
* **Data Validation:** (Yes/No – for dropdown lists to select codes)
* **Year-to-Date Summary:** (Yes/No)
* **[8. Output Format]:** Choose one:
* **A) Step-by-Step Guide:** Provide a detailed, text-based guide on how to build the tracker from scratch, including table structures, exact cell formulas, conditional formatting rules, and chart setup instructions.
* **B) VBA Macro Code:** Provide a complete, commented VBA macro that, when run in Excel, will automatically generate the entire workbook, including all sheets, tables, formulas, and formatting.
—
### **PART 2: DETAILED TEMPLATE STRUCTURE & REQUIREMENTS (Use these as your guide)**
Based on the inputs from Part 1, construct the Excel workbook with the following structure:
**Sheet 1: `Attendance_Tracker_[Month]_[Year]` (e.g., `Attendance_Tracker_July_2024`)**
1. **Header:** Display `[Company Name]` and the `[Tracking Period]`.
2. **Employee Data Columns (A & B):**
* `A: Employee ID`
* `B: Employee Name`
3. **Daily Tracking Columns (C onwards):**
* Create one column for each day of the specified month (e.g., 1, 2, 3… 31).
* The header for each day should also display the day of the week (e.g., “1-Mon”, “2-Tue”).
* Automatically apply gray shading to columns corresponding to non-working days (weekends) based on `[Work Week]`.
* Use **Data Validation** to create a dropdown list in each daily cell, populated with the `[Attendance Codes]`.
4. **Summary Columns:**
* Calculate totals for each employee for each `[Attendance Code]`. For example: `Total Present`, `Total Sick Leave`, `Total Vacation`, etc.
* Use the `COUNTIF` or `COUNTIFS` function for accurate counting.
* Include a `Total Payable Days` column (e.g., Present + Holiday + Sick Leave + Training).
5. **Conditional Formatting:**
* Apply color-coding to the daily tracking cells based on the selected attendance code. (e.g., P = Green, A = Red, SL = Yellow, V = Blue, H = Gray). This should be visually intuitive.
**Sheet 2: `Dashboard` (If requested in `[Key Features Required]`)**
1. **Key Performance Indicators (KPIs):**
* Overall Attendance Rate % for the month.
* Total Absences This Month.
* Most Frequent Leave Type.
2. **Charts & Graphs:**
* **Pie Chart:** “Leave Type Distribution” showing the proportion of all leave types (`SL`, `V`, `A`, etc.).
* **Bar Chart:** “Absences by Employee” to quickly identify employees with high absenteeism.
* **Line Chart (Optional):** “Daily Attendance Trend” showing the number of present employees each day of the month.
3. **Data Slicers (if applicable):** Allow filtering the dashboard by Employee or Department (if department data is provided).
**Sheet 3: `Settings`**
1. **Employee List Table:** A named Excel Table (`tbl_Employees`) containing the `[Employee List]`. The main tracker sheet should pull names from here.
2. **Attendance Codes Table:** A named Excel Table (`tbl_Codes`) with two columns: `Code` and `Description`. This table will be the source for the data validation dropdowns.
3. **Holidays Table:** A named Excel Table (`tbl_Holidays`) with a list of `[Holiday List]` dates. The main tracker sheet should use this list to automatically populate the “H” code and apply formatting.
### **PART 3: FINAL INSTRUCTIONS**
* **Formulas:** Ensure all formulas are dynamic and robust. Use Excel Tables and structured references (e.g., `=COUNTIF(tbl_Tracker[@[1]:[31]],”P”)`) instead of static ranges like `C2:AG2`. This makes the template scalable.
* **Clarity:** If providing a Step-by-Step Guide (Output A), be extremely clear and precise. If providing VBA (Output B), ensure the code is well-commented so a user can understand the different parts of the script.
* **Best Practices:** Do not use merged cells. Ensure the layout is clean, professional, and print-friendly. Lock protected cells on the final sheets to prevent accidental formula deletion.
**Generate the output now based on the filled-in dynamic inputs.**