Attendance and Schedule Formulas
Track hours, shifts, and absences.
The Open buttons send the prompt text as currently shown below to that site only when you click. Fill in the placeholders first.
Write formulas for attendance and schedules. App: [SPREADSHEET_APP]. Layout (names, dates, start and end times, breaks, leave types): [LAYOUT]. Rules (overtime thresholds, rounding, paid breaks, holidays): [RULES]. Sample data (fake): [SAMPLE_DATA]. Provide a table: Need | Formula | Explanation | Example result. Cover: hours worked from start and end times, including shifts that cross midnight (=MOD(end-start,1)) and subtracting breaks; converting time to decimal hours (*24); rounding to the nearest quarter hour (MROUND or ROUND); regular versus overtime hours with thresholds per day and per week; weekly and monthly totals (SUMIFS); counting absences by type; attendance rate; late arrivals flag (compare with scheduled start); lunch deduction rules; scheduled versus actual hours variance; coverage check (number of staff per hour, COUNTIFS); and holiday handling. Add: formats (time versus number) and why totals over 24 hours need the [h]:mm format; protection against typos with validation; a monthly summary layout; privacy cautions (only collect what you need; restrict access to personal data). Note that pay and overtime rules differ by place; verify with the rules in [JURISDICTION] before using for pay. Include eight test cases with expected output.
Fill in before use
Optional: type your details and the prompt above updates. Nothing is saved or sent.
Replace each bracketed placeholder with your own details, then paste into Any AI or another assistant.