# 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.