Calculating with time in Excel: how it works
To Excel, a time is part of a day: 12:00 is 0.5. That's how you work out how long someone worked, with =C2-B2, and with *24 you turn it into hours, for the pay for example.
Try the first exercises for freeIn the lesson Calculating with times and hours
How does Excel calculate with time?
To Excel, a time is part of a day. 12:00 is 0.5, 6:00 is 0.25 and 18:00 is 0.75. If you type 8:30 into a cell, Excel stores 0.354 and shows 8:30. That's why you can calculate with times, just like with numbers.
End minus start gives the time in between: =C2-B2. If you want hours as a number, for example to multiply by an hourly rate, multiply by 24, because a day has 24 hours: =(C2-B2)*24 turns 8:30 into the number 8.5.
When do you calculate with time?
- How long someone worked, from start to finish: =C2-B2.
- The pay for a day, with the hourly rate in D2: =(C2-B2)*24*D2.
- The time worked without the break, with the break as 0:30 in D2: =C2-B2-D2.
- The total for a week, in the format [h]:mm: =SUM(B2:B6).
- A night shift that ends after midnight: =MOD(C2-B2,1).
Calculating hours worked, step by step
D2 contains =C2-B2. From 8:30 to 17:00, Lucy worked 8 hours and 30 minutes. Change the numbers or the formula and see what happens.
This is how to go about it:
- Type the start time and the end time with a colon, such as 8:30 and 17:00. Excel recognises them as times straight away.
- Click D2 and subtract the start from the end.
=C2-B2 - If you see a number such as 0.354, give the cell a time format: press Ctrl + 1, or Cmd + 1 on a Mac, and choose the Time category. Now it says 8:30.
- If you want the hours as a number, multiply by 24 and choose the Number format: 8.5.
More examples
Hours as a number
D2 contains =(C2-B2)*24. With *24, 8:30 hours becomes the number 8.5, which you can keep calculating with, such as with an hourly rate.
Adding up a week, over 24 hours
B7 contains =SUM(B2:B6). In the format [h]:mm you see 41:30. Without the square brackets, Excel would show 17:30.
A night shift past midnight
D2 contains =MOD(C2-B2,1). 6:30 minus 22:00 is negative. MOD adds a whole day: 8 hours and 30 minutes.
Hours as a number
If you want to keep calculating with the hours, for the pay for example, multiply by 24: =(C2-B2)*24 turns 8:30 into the number 8.5. Then give the cell the Number format.
More than 24 hours
If you add up the hours of a week, choose the [h]:mm format. Without the square brackets, Excel starts again at 0 after 24 hours, and you see 17:30 instead of 41:30.
Where it often goes wrong
- If a shift ends after midnight, end minus start gives a negative number. Use =MOD(C2-B2,1).
- Type a time with a colon: 8:30. Typed as 8.30, it's just a number.
Practise this in the lesson Calculating with times and hours. The first exercises are free.
Frequently asked questions
Why do I see 0.354 instead of 8:30?
Then the cell has the General or Number format. Choose a time format, and you'll see 8:30 again.