A DTR (daily time record) shows when each employee started, took a break and finished on each day. Many Philippine businesses keep it in Excel or Google Sheets. For a small team at one site, that is a sensible start. It is free, everyone knows how to use it, and you can print it for a signature.
This guide shows you how to build a DTR template that holds up, where it starts to fail, and how to know when an app is worth it.
The columns a good DTR template needs
Use one row per employee per day. Keep one sheet per cut-off period. These columns cover most teams:
| Column | What goes in it |
|---|---|
| A. Date | The work date. For a shift that crosses midnight, use the date the shift started. |
| B. Employee | Name or employee number, typed the same way every time. |
| C. Scheduled in | The start time of the shift on the roster. |
| D. Scheduled out | The end time of the shift on the roster. |
| E. Time in | The actual clock-in time. |
| F. Break out | The start of the meal break. |
| G. Break in | The end of the meal break. |
| H. Time out | The actual clock-out time. |
| I. Hours worked | A formula. See below. |
| J. Late (minutes) | A formula. See below. |
| K. Remarks | Leave, a forgotten punch, a site visit, or the reason for a change. |
| L. Checked by | The initials of the supervisor who checked the row. |
Format columns C to H as time (for example h:mm AM/PM), so that Excel stores them as times and not as text.
Formulas that handle the hard cases
Hours worked, including overnight shifts. A plain =H2-E2 gives a negative result when someone works from 10:00 PM to 6:00 AM. Use MOD instead. It wraps the time around midnight:
=(MOD(H2-E2,1)-MOD(G2-F2,1))*24
This gives the hours from time in to time out, less the break, as a decimal number such as 8.5.
Late minutes. This counts only the minutes after the scheduled start, and shows zero for an early arrival:
=MAX(0,ROUND((E2-C2)*1440,0))
This formula assumes that the scheduled start and the time in fall on the same date. If a night shift starts near midnight and the person arrives after midnight, check the late minutes by hand.
If your company allows a grace period, that is your own policy. Write it in your attendance policy and apply it the same way for everyone. How late minutes, undertime and overtime affect pay is a separate question. Check the current DOLE rules or ask a payroll specialist before you build pay into the sheet.
Missing punches. Add a check column that flags a row with a time in but no time out:
=IF(AND(E2<>"",H2=""),"MISSING OUT","")
Then use conditional formatting to colour the flagged rows, so they stand out before cut-off.
Protect the sheet
- Lock the formula columns so nobody types over them by accident.
- Use data validation on the time columns to refuse text such as "8am".
- Keep a read-only copy of each period after it closes. Name it with the period dates.
- Turn on version history if you use Google Sheets or OneDrive, and know how to open it.
When a spreadsheet DTR works
A spreadsheet is often enough when most of these are true:
- You have a small team at one site.
- Shifts are the same most days, and few of them cross midnight.
- The owner or one supervisor sees staff arrive and leave.
- One person keeps the file, and nobody else edits it.
Where it breaks down
The problems grow with the number of people, sites and shifts. These are the common ones:
- The time comes from memory. If staff write their own times, or a supervisor types them in at the end of the week, the DTR records what people recall, not what happened.
- Edits leave no trace. Anyone with the file can change 8:20 to 8:00. Version history helps only if someone looks, and it does not say why.
- Missing punches pile up at cut-off. Nobody notices a forgotten clock-out until payroll asks. By then, nobody remembers the real time.
- Overnight and split shifts confuse formulas. One wrong date or a copied formula gives the wrong hours for a whole column.
- Many sites means many files. Each branch sends its own version, in its own layout, by email or chat. Someone has to merge them by hand.
- Nobody approves the day. A sheet has no clear point where a supervisor says "this day is correct". Payroll gets numbers that nobody signed off.
What a time clock app changes
A time clock app is not magic, but it changes where the time comes from and what happens to it:
- The punch is recorded at the moment it happens, by the employee, on a phone or a browser.
- The original punch stays. A supervisor can add a missing time or remove a wrong one, but the change is a separate record with a reason and a name. The first record is kept.
- Exceptions show up the same day. Late starts, early finishes and missing punches are flagged against the published shift, so they get fixed while people remember.
- Each day gets an approval. A supervisor approves the day once it is right. Payroll works from approved days only.
- All sites use one format. There is nothing to merge.
How to switch without disruption
- Pick one site or one team to start.
- Run the app and the spreadsheet side by side for one full cut-off period.
- Compare the hours for each person at the end. Look into every difference.
- Fix your shift times and settings, then move the next site.
- Keep your old DTR files. Do not delete history when you switch.
Tell staff why you are making the change. Most people like a record they can see for themselves, because it protects them as much as the company. For a full plan, read how to roll out a time clock app. If you move payroll as well, follow the payroll parallel run checklist.
Where Workforce Pinas fits
Workforce Pinas keeps an online DTR for each person. Staff clock in on the Android app or in a browser, and the iPhone app is coming soon. If the signal drops, the phone keeps the punch with its original time and sends it later. Managers see the days that need a decision first. Each correction needs a reason, the original punch stays, and approved days go to payroll review. See DTR and timesheets for how it works, or ask us for a demo.
To make the records harder to game, read how to prevent buddy punching. To build the shifts that your DTR is checked against, see how to make a shift roster.
