Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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.

Here's my spreadsheet:

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.

share|improve this question
    
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
1  
@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 Jul 19 '12 at 23:53
    
True, but who works over 24 hrs straight? :) – Darius Jul 19 '12 at 23:59
1  
@Darius, it's more relevant when you're summing a week or two which is pretty typical for payroll. :-) – Daniel 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:

enter image description here

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
share|improve this answer
    
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.

share|improve this answer
    
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.

share|improve this answer
    
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)
share|improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .