# How to calculate how much I've worked per day?

I'm having a problem when I try to calculate how much time I've worked per day by using an Excel formula. It returns valid results for when I've worked until 12:00 PM but if I passes it, values come negative.

```Started at          Left at         Total worked
12:00 PM            7:00 AM         8
12:00 PM            1:00 AM         -11
```

The formula I'm using:

`=INT((B1-A1)*24)`

I'm pretty sure this is simple but I can't figure a nice way to accomplish this.

Any help is welcome!

Thanks.

-
Maybe this can help. Or this. – criziot Jul 19 '12 at 23:35
Personally I would enter date + time in a following manner mm/dd/yyyy hh:mm:ss and then subtract those cells. The cell that holds the subtraction formula needs following format: hh:mm:ss – Darius Jul 19 '12 at 23:46
@Darius I prefer using `[h]:mm:ss` because that will display the number of hours over 24. Using hh:mm:ss will only display up to 24, and will effectively show the number of hours mod 24. This can be very misleading. – Daniel Cook Jul 19 '12 at 23:53
True, but who works over 24 hrs straight? :) – Darius Jul 19 '12 at 23:59
@Darius, it's more relevant when you're summing a week or two which is pretty typical for payroll. :-) – Daniel Cook Jul 20 '12 at 0:21

Try this formula:

``````=24*(B1+(A1>B1)-A1)
``````

Where:
`A1` is your Start Time ("Started At")
`B1` is your End Time ("Left At")

It'll help you compute the number of hours elapsed between 2 time stamps. It should work even if your "End Time" is past midnight (or even 2 days later). Don't forget to set the cell format to General or `0.00`.

Some examples:

If you want to get just the time elapsed (non-decimal or in `[h]:mm:ss` format), use the formula below and change the cell format to Custom > `[h]:mm:ss`.

``````=B1+(A1>B1)-A1
``````
-
What does the `(A1>B1)` bit do? – Douglas Jul 20 '12 at 21:07
@Douglas It adds 1 (aka 24 hours) to B1 if the time in A1 is greater than B1 (when the End Time is past midnight). – Kaze Jul 20 '12 at 21:13
@zpea Yep. Took me a while to dig one of my old answers that included this formula. – Kaze Jul 20 '12 at 22:41

The problem is that the way you're storing your time, Excel treats it as a number between 0 and 1. I'm sure you realize that, but that means that if you leave at some time AM, but arrive in the PM then you'll get a negative number.

To avoid this, you can use something like this:

``````=(IF(B1>A1,B1-A1,B1+1-A1))*24
``````

This will subtract your arrival time from your departure time correctly. If Excel thinks your departure time is before your arrival, the +1 will push it to the next day as desired.

-
Argh, you beat me to it – Canadian Luke Jul 19 '12 at 23:40
Thanks, Daniel. It makes total sense. – Rafael Rinaldi Jul 19 '12 at 23:44

The only way that I know how is to use military time.

-
Even with using Military time, the OP's original formula would still return a negative value for time stamps that cross midnight. :( – Kaze Jul 21 '12 at 4:06

You can add times using the `=SUM` worksheet function. Just enter all of your times as HH:MM:SS, and then use SUM to add them up. You may leave off the :SS if you prefer. By default, Excel will display the sum of times in "time-of-day" format, meaning that adding 12:30 + 12:45 will yield 01:15. You can prevent Excel from "rolling over" at 24 hours by formatting the result cell as [h]:mm which will cause it to display 25:15 rather than 01:15.

If you want to add up minutes and seconds, you must include a leading "0:" in your data.

For example, enter "0:10:20" to indicate 10 minutes, 20 seconds. When you sum these times, Excel will display the sum in "time-of-day" format, meaning that adding 0:40:10 and 0:30:20 will yield 1:10:30. You can prevent Excel form "rolling over" at the hour by formatting the result cell as [m]:ss which will cause it to display 70:30 rather than 1:10:30.

Another method of adding times is to use the TIME function. To add 1 hour, 35 minutes, 10 seconds to a time in A1, use the function:

``````=A1 + TIME(1,35,10)
``````
-