Calculate hours from checked punches

Turn an IN at 2:30:15 PM and an OUT at 10:45:15 PM into 8.25 hours.

What you'll make

In Excel: IN 11/4/2026 2:30:15 PM, OUT 10:45:15 PM becomes 8.25 hours.

The formula

The formula, with a numbered bracket under each part.
NumberPartWhat it doesResult so far
1A8=""Checks if the IN punch is empty.No
2B8=""Checks if the OUT punch is empty.No
3B8<A8Checks if OUT is earlier than IN.No
4(B8-A8)*24Time between punches, times 24 for hours.8.25
5IF( … )Flags part 1, 2 or 3, else shows part 4.8.25

A Review message shows when part 1, 2 or 3 says yes. With both punches empty, it says No punches supplied.

=IF(A8="",IF(B8="","No punches supplied","Review missing IN punch"),IF(B8="","Review missing OUT punch",IF(B8<A8,"Review reversed full punches",(B8-A8)*24)))

How to use it

Yellow bar at the top? Click Enable Editing.

  1. See it work. The Example tab shows 20 made-up examples with the formula already in.
  2. Use your own punches. Paste each IN and OUT punch, with its date and time, into the green columns of the Template tab. The hours fill in by themselves.
  3. Move them into your letter. Copy the results. Then use Paste Special and choose Values.

Before you send

Use full dates with times in one time zone. These are hours between punches, which may differ from paid hours.

Microsoft's help: IF function