0

I am working on a work sheet. Sometimes I work late and sometimes long hours (outside the 24 hour time frame).

I am looking for a automated solution in Excel where I can easily calculate the total number of worked hours with those the scenario below.

Note: I have formatted the result cell in: [h]:mm format.

started working: 23:05

stopped working: 01:35

Total number of hours worked: 02:00

6
  • It is unclear what you are asking. Which numbers are you trying to subtract from which? What have you tried and what was the result or error? – techturtle Nov 20 '17 at 18:34
  • 3
    How do you get 26:25 from the first example? If you may be working longer than 24 hours I think you need to include the date in both start and end time, then you can just subtract one from the other for worked hours – barry houdini Nov 20 '17 at 18:58
  • @Alexander Are you only expecting the complete hours worked? as the example you show (23.05 and 01.35) is 2.5 hours... Anyway, Barry has given you the solution... – Solar Mike Nov 20 '17 at 19:51
  • Was not as easy as someone claimed it to be. I solved it w/ a conditional statement. – Alexander Nov 20 '17 at 20:13
  • Would you like to give your solution as an answer - it may help others with similar puzzles? – barry houdini Nov 21 '17 at 1:03
1

Assume

  • You are working on some time sheet, which each working period is not longer than 24 hours.
  • A1 stores start working time (without date part, say 23:05).
  • B1 stores stop working time (without date part, say 01:35).

Answer

Input the formula into C1, then format it as [h]:mm.

= B1 - A1 + IF(B1 < A1, 1, 0)

You will get the duration of the working time in C1, say 2:30 (2hr 30mins).


Explanation

Normally, B1 - A1 is the duration between start & stop working time.

But, if B1 < A1, it means that the worker worked pass midnight, we need to add 1 (day) to the result.

0

This seems to work : format the cells as date and time, or only time and take the difference.

Unlike another answer posted this give a duration of 2hrs 30 mins which should be correct...

Here is an image showing it working : enter image description here

You can also format as time and it still works: enter image description here

1
  • What if his sheet contains only time information, but not dates? – Máté Juhász Nov 21 '17 at 10:26
0

Since my previous solution was not calculating correct result. In fact was not including 55 Minutes, so that I'm posting this one.

Now using the Data which are Cell A2 is 20/11/2017 23:05 & in Cell B2 is 21/11/2017 01:35, the following formula will count the correct difference of the Time.

D2 = (B2-A2).

And the trick is Format the Result Cell like [hh]:mm.

Answer will 2:30Hrs ,will count the Date difference also.

-1

For better result I would like to suggest you to do the followings,

  1. Format both the Cells for DD/MM/YY HH:MM:SS.

  2. Format the Formula Cell as HH:MM:SS.

Suppose in Cell A2 Start Date & Time is 20/11/2017 23:05 and in B2 End Date & Time is 21/11/2017 01:35.

Write the Formula in D2 ,

=TIME(HOUR(B2),MINUTE(B2),SECOND(B2)-TIME(HOUR(A2),MINUTE(A2),SECOND(A2)))

The answer I got is 01:34:59.

NB: To make the combination with your suggest Time, I've added recent dates also because, in case of more than 1 day's difference Excel will not able to calculate the real time difference.

My Formula calculates accurately rather than other.

Like if you use Formula = INT((B2-A2)*24) the answer will 2, [when the Cell Format is General & if you covert it into HH:MM you get 00:00]

Hope this help you, I've posted the solution after is been tested by me.

6
  • Are you missing 55 minutes in the example? 23.55 to midnight is 55 minutes and then midnight to 01.35 is 1hr 35mins... – Solar Mike Nov 21 '17 at 8:09
  • @Solar, No I did not. Originally the Start Time is 23:05 (24 Hrs Clock) if I simplify is 11 hrs & 5 Minutes and the End Time is 1:35. – Rajesh S Nov 21 '17 at 8:20
  • So, if you use the 24h clock then 13 - 11 is 2.... 13.35 - 11 is 2.35 and take into account the 5 minutes is then 2hrs 30 mins – Solar Mike Nov 21 '17 at 8:27
  • @Solar ,, Not again 23:05 means 11 Hrs & 5 Minutes (11:05 PM) at Night. And 01:35 is 1Hrs. 35Mnts is AM (earlier in Morning). Like Current Time is 14:30 (24 Hrs Clock) is 02:30 Pm (12 Hrs Clock), Just Check this ,onlineconversion.com/date_12-24_hour.htm – Rajesh S Nov 21 '17 at 8:59
  • So, you are saying the 55 minutes worked prior to midnight does not count... I won't work for you : cheapskate... – Solar Mike Nov 21 '17 at 9:04

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