Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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 Cook 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 Cook Jul 20 '12 at 0:21

4 Answers 4

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). –  Ellesa Jul 20 '12 at 21:13
    
@zpea Yep. Took me a while to dig one of my old answers that included this formula. –  Ellesa 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. :( –  Ellesa 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

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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