2

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.

7
  • Maybe this can help. Or this.
    – fmanco
    Jul 19, 2012 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, 2012 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, 2012 at 23:53
  • True, but who works over 24 hrs straight? :)
    – Darius
    Jul 19, 2012 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, 2012 at 0:21

4 Answers 4

5

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
3
  • What does the (A1>B1) bit do?
    – Douglas
    Jul 20, 2012 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, 2012 at 21:13
  • @zpea Yep. Took me a while to dig one of my old answers that included this formula.
    – Ellesa
    Jul 20, 2012 at 22:41
4

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.

1
  • Argh, you beat me to it Jul 19, 2012 at 23:40
1

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

1
  • 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, 2012 at 4:06
1

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)

You must log in to answer this question.

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