How do I add time like 8:20am-12:00pm and then after lunch break 12:30pm-5:02pm to end of day to get hours worked per day? I was using =(B2-A2*24)+(D2-A2*24) but that doesn't work

  • 2
    Is that to be read as two timespans (8:20-12:00 and 12:30-05:02), or as four different periods (8hrs 20min, 12hrs 0min, 12hrs 30min, 5hrs 2min)? What is the expected result of your formula? Nov 14 '13 at 15:48
  • I see a couple of issues here. Why are you multiplying by 24? Your 5:02 should be 17:02 unless you're including a "PM" in there. This all assumes that these are times and not durations, as Vidar has asked about. Please edit your question to be more specific so we might offer some concrete help.
    – ale
    Nov 14 '13 at 16:47
  • Why don't you use this example made on Web Applications: webapps.stackexchange.com/q/45039/29140 Nov 14 '13 at 17:15

It looks like you've got some order of operations trouble. I think this will give you what you want:


Of course, you also need to ensure that the cell uses a decimal number format. And that your afternoon values need to be actual afternoon values. You need to use 24-hour format or am/pm format.

In any event, using your values above, this formula, and appropriate formatting, this is what I get:

 8:20 | 12:00 | 12:30 | 17:02 | 8.20
  • I have changed the format to time AM/PM and this is how I have the spreadsheet set up A1 8:20 AM B1 12:00 PM C 12:30 PM D1 5:02 PM and formatted the E cell to decimal and used your formula and I get -3.80 for an answer? What am I doing Wrong?
    – Lori
    Nov 15 '13 at 3:24
  • I don't know. Maybe Google Spreadsheets is assuming different days? What do you see if you format the cells to show date as well as time?
    – ale
    Nov 15 '13 at 18:08

The answer, concerning your working hours, should be:

8 hours and 12 minutes ==> 8:12.

as per time notation.

If you use the following data:

 8:20 am | 12:00 pm | 12:30 pm | 5:02 pm | 

and use the following formula


then the result would be 8.2. This is not a time notation !!

If you convert it to a 24h notation (menu Format)

 8:20 | 12:00 | 12:30 | 17:02 | 

and use the following formula


then the result would be 8:12.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.