Skip to content

DTR template in Excel or a time clock app?

A spreadsheet DTR can work well for a small team. Here is a layout and formulas you can copy, the problems to watch for, and the signs that it is time to switch.

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:

ColumnWhat goes in it
A. DateThe work date. For a shift that crosses midnight, use the date the shift started.
B. EmployeeName or employee number, typed the same way every time.
C. Scheduled inThe start time of the shift on the roster.
D. Scheduled outThe end time of the shift on the roster.
E. Time inThe actual clock-in time.
F. Break outThe start of the meal break.
G. Break inThe end of the meal break.
H. Time outThe actual clock-out time.
I. Hours workedA formula. See below.
J. Late (minutes)A formula. See below.
K. RemarksLeave, a forgotten punch, a site visit, or the reason for a change.
L. Checked byThe 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:

  1. The punch is recorded at the moment it happens, by the employee, on a phone or a browser.
  2. 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.
  3. 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.
  4. Each day gets an approval. A supervisor approves the day once it is right. Payroll works from approved days only.
  5. All sites use one format. There is nothing to merge.

How to switch without disruption

  1. Pick one site or one team to start.
  2. Run the app and the spreadsheet side by side for one full cut-off period.
  3. Compare the hours for each person at the end. Look into every difference.
  4. Fix your shift times and settings, then move the next site.
  5. 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.

See how it could work for your team.

Looking for a better way to manage people and shifts? Tell us a little about your business and we can discuss a demo and early access.

Pricing is being finalised. Ask us about pilot pricing and what is available today.

We will reply by email, learn about your team and show you a demo organisation.

Prefer to explore the web app? Create an account.

Contact the Workforce Pinas team directly: [email protected]

We use these details only to answer your enquiry. See our privacy policy.