Calculating time worked per day

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

• 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:

=((B2-A2)*24)+((D2-C2)*24)

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

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

=((B1-A1)+(D1-C1))*24

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

=(B2-A2)+(D2-C2)

then the result would be 8:12.