Formula to get the worked hours from time A to time B.

[ShadowHash]

Hi, I'm using excel to write down my shifts and get a total of worked hours per day and per week.
I have it structured this way:

screen.png

Everything seemed to work fine until I finished to work at 12.30 am.. The result with my formula was -17 hours instead of 7. How can I fix my formula so that it displays a correct amount?

[FDibbins]

Please upload a sample of your workbook, not a picture of your data. Pictures are pretty much impossible to edit, and no-one wants to re-type your data for you
Also, not all members can upload picture files (Company firewalls and stuff) - and, depending on what browser is being used, some pics dont even show up on the forum

Your workbook should show a small desensitized example of the data you are working with and a manual mockup of the expected results.

Having said that, see if this will help...

	A	B	C
3	3:00 PM	11:00 PM	8:00
4	12:00 PM	1:00 AM	13:00
5	3:00 PM	1:00 AM	10:00

C3=IF(B3>A3,B3,B3+1)-A3
copied down
(I know there are other ways, but this wirks)

[ShadowHash]

Please upload a sample of your workbook, not a picture of your data. Pictures are pretty much impossible to edit, and no-one wants to re-type your data for you
Also, not all members can upload picture files (Company firewalls and stuff) - and, depending on what browser is being used, some pics dont even show up on the forum

Your workbook should show a small desensitized example of the data you are working with and a manual mockup of the expected results.

Having said that, see if this will help...

	A	B	C
3	3:00 PM	11:00 PM	8:00
4	12:00 PM	1:00 AM	13:00
5	3:00 PM	1:00 AM	10:00

C3=IF(B3>A3,B3,B3+1)-A3
copied down
(I know there are other ways, but this wirks)

You were right about the file I should have uploaded. I tried your formula but something is wrong either on my side or yours. (definitely on mine). I attached the file here, hope it helps. It's the weird result in the row 11 where it supposed to be 7 hours, it shows 0,79...
http://cl.ly/1g2A021e1m1h/working%20shifts.xlsx

Thanks for helping!

[Special-K]

It's because the time has gone past midnight.

Try this formula on the column instead

=IF(C3<B3,(1-B3)+(C3-0.5),C3-B3)-D3*24

0.5 is the Excel time equivalent of 12:00 PM (midnight)

[ShadowHash]

It's because the time has gone past midnight.

Try this formula on the column instead

=IF(C3<B3,(1-B3)+(C3-0.5),C3-B3)-D3*24

0.5 is the Excel time equivalent of 12:00 PM (midnight)

That transforms the cell into Number H:MM format while I want it to be a simple generic number, otherwise it mistakenly displays the overall hours for selected days as 16.7916667 instead of 16.75.

Perhaps the niks28 solution would be easier one if there is nothing else that can be done.

[ShadowHash]

Small update:

@niks28: your solution doesn't work since 24:00 is displayed as 0:00 thus makes the result still negative 0:00 - 16:00

The Special-K solution doesn't work either because since all my numbers are in 'general number' format, the result of total hours makes this: 5+5.5+5.5+7:00PM makes something like 16,79... because 7:00PM in number format is 0,29... and I don't even understand why on earth that formula transforms the cell into TIME format where other cells keep staying in number format.


EDIT: I might have found a solution on another site:

=IF(C11=0,0,(MOD((C11-B11)-D11,1))*24)

This one solves my problem. It shows up as an inconsistent formula though, but working.

[ShadowHash]

Hi, I've discovered a small bug in my formula.

So I sorted out with the MOD function to calculate my worked hours where I used to start at XX PM/AM and finish at XX AM/PM where without the MOD function I could not calculate properly if I was starting like at 5PM and finish after midnight.
Anyway, not it works properly if I finish working before midnight or after. But If I finish working exactly at midnight and put in the cell 12 AM value, it gives me as a result 0 using this formula: =IF(C41=0,0,(MOD((C41-B41)-D41,1))*24). But if I put as value 12 and press enter, it gives me the correct amount of worked hours, but it also changes the value to 12/01/1900 12:00:00 AM. Why is that?

You can see the problem in 1, 2 and 9 October rows :

XLS file : http://cl.ly/2Y3h1A3T3f2y/working%20shifts.xlsx

[Tony Valko]

I didn't download your file.

12:00 AM has the numeric value of 0.

So:

=IF(C41=0,0,(MOD((C41-B41)-D41,1))*24)

If C41 = 12:00 AM then C41=0 = TRUE and returns 0.

Maybe you just need to test for an empty cell:

=IF(C41="",0,MOD((C41-B41)-D41,1)*24)

[ShadowHash]

12:00 AM has the numeric value of 0.

If C41 = 12:00 AM then C41=0 = TRUE and returns 0.

Maybe you just need to test for an empty cell

Thanks, you saved my life!

[Tony Valko]

You're welcome. Thanks for the feedback!