How Excel stores times

Times are fractions of a day. Add minutes with TIME, take a time apart with HOUR and MINUTE, and make it readable with a time format. Beginner · 4 min · Fractions of a day, TIME, HOUR / MINUTE, h:mm AM/PM

Excel stores a time as a fraction of a day. Midnight is 0, 6:00 AM is 0.25, noon is 0.5 and 6:00 PM is 0.75. A time format turns that fraction into something people can read, like 7:30 AM. It's the same idea as dates: the whole number counts the days, and the fraction is the time of day.

Because times are numbers, you can add to them. The catch is the unit: adding 1 adds a whole day, so minutes and hours need converting first. That's the job of TIME(hours, minutes, seconds).

The sheet is a dog walker's pickups for one day, with the length of each walk in minutes.

Steps

First, a look behind the format. B9 has no number format. Type =B5 there to see what Excel really stores for Otis's 12:00 PM pickup.
When does each dog get home? In D2:D7, add the walk to the pickup time. TIME(0, C2, 0) turns the minutes in C2 into a time (0 hours, C2 minutes, 0 seconds), so use =B2+TIME(0, C2, 0).
Decimals mean nothing to a dog owner. Select D2:D7 and pick Time from the number-format menu in the toolbar.
The booking app wants the drop-off hour and minute in separate columns. In E2:E7, use HOUR on the drop-off time in column D. In F2:F7, use MINUTE.

Remember

Times are fractions of a day: 0.25 is 6:00 AM, 0.5 is noon.
TIME(h, m, s) builds a time; add it to move a time on: =B2+TIME(0, 45, 0).
A minute is 1/1440 of a day, so =B2+45/1440 does the same job.
HOUR and MINUTE take a time apart; a Time format makes the fractions readable.

Previous: Loan payments and savings growth · Next: Timesheets and hours worked