working & overtime hrs

[Trevor3007]

Hi,
I have attempted to solve this myself but could not & hence why I am reaching out to Mr Excel.

Case
Create a time table whereby it would display the hours worked over 8 hrs: -

Below is an example

Start	End	Normal Day Hours	OverTime
07:00​	16:00​	8​	##############​

As you can see, the overtime col does not display correctly .

Please can some kind ' Mr Excel' person resolve this for me?

Thank you .

 

[hagia_sofia]

Hello, what does this return:

=(IF(B2>A2,B2-A2,1-A2+B2))-C2

 

[Trevor3007]

Hello, what does this return:

=(IF(B2>A2,B2-A2,1-A2+B2))-C2

thank you for your help.

unfortunately, i get the same result as per mine

Start	End	Normal Day Hours	OverTime
07:00​	16:00​	8​	##############​

much appreciated..thanks again.

 

[hagia_sofia]

And what if you manually enter the relevant times and compare them to the original values - does it alway return TRUE?

 

[AhoyNC]

Try:

Book1
	A	B	C	D
1	Start	End	Normal Day Hours	OverTime
2	7:00	16:00	8	1:00
3	7:00	10:00	8	0:00
4	7:00	1:00	8	10:00
Sheet2

Cell Formulas
Range	Formula
D2:D4	D2	=IF(MOD(B2-A2,1)<=C2/24,0,MOD(B2-A2,1)-C2/24)

 

[Trevor3007]

Try:

Book1
	A	B	C	D
1	Start	End	Normal Day Hours	OverTime
2	7:00	16:00	8	1:00
3	7:00	10:00	8	0:00
4	7:00	1:00	8	10:00
Sheet2

Cell Formulas
Range	Formula
D2:D4	D2	=IF(MOD(B2-A2,1)<=C2/24,0,MOD(B2-A2,1)-C2/24)

Thank you soooooooooooooooooo much. that works greeeeeeeeeat

 

[AhoyNC]

You're welcome. Thanks for the feedback.

 

[Trevor3007]

And what if you manually enter the relevant times and compare them to the original values - does it alway return TRUE?

And what if you manually enter the relevant times and compare them to the original values - does it alway return TRUE?

thank you. My issue has now been resolved by AhoyNC.. But I do appreciate your help