Time Calculation

[uswyne]

Hello,
Please help me for putting excel formula in given sheet column C.
Thanks

A​	B​	C​
Time IN (07:00 Am) Calculate Time before 7:00 AM​	Time Out (07:00 PM)​ Calculate Time before 7:00 PM​	Over Time​
6:30 AM​	8:00 PM​	1.5 this result is needed ​

 

[Sufiyan97]

Try

Book1
	A	B	C	D	E
1	Time IN (07:00 Am)	Time Out (07:00 PM)	Over Time
2	Calculate Time before 7:00 AM	Calculate Time before 7:00 PM
3	6:30 AM	8:00 PM	1.5	1.5 this result is needed
4
5
Sheet5

Cell Formulas
Range	Formula
C3	C3	=(TIME(7,0,0)-A3+B3-TIME(19,0,0))*24

 

[uswyne]

Try

Book1
	A	B	C	D	E
1	Time IN (07:00 Am)	Time Out (07:00 PM)	Over Time
2	Calculate Time before 7:00 AM	Calculate Time before 7:00 PM
3	6:30 AM	8:00 PM	1.5	1.5 this result is needed
4
5
Sheet5

Cell Formulas
Range	Formula
C3	C3	=(TIME(7,0,0)-A3+B3-TIME(19,0,0))*24

Thanks, but there is problem if the time out is less than 1900 hrs reult shows Minus value.

 

[Sufiyan97]

See if this works.

Book1
	A	B	C	D
1	Time IN (07:00 Am)	Time Out (07:00 PM)	Over Time
2	Calculate Time before 7:00 AM	Calculate Time before 7:00 PM
3	6:30 AM	8:00 PM	1.5
4
5
6
Sheet5

Cell Formulas
Range	Formula
C3	C3	=IF(B3-A3<TIME(12,0,0),0,((B3-A3)-(TIME(19,0,0)-TIME(7,0,0)))*24)

 

[Alex Blakenburg]

If it is the same issue at both the start and end times then maybe:

=(MAX(TIME(7,0,0)-A3,0)
 +MAX(B3-TIME(19,0,0),0))*24

 

[Sufiyan97]

Hi Alex

I think OP wants to calculate overtime if above 12 hours 7 AM and PM

If that is the case, please see below

Book1
	A	B	C	D
1	Time IN (07:00 Am)	Time Out (07:00 PM)	Over Time
2	Calculate Time before 7:00 AM	Calculate Time before 7:00 PM
3	6:30 AM	5:00 PM	0
4			0.5
5
Sheet5

Cell Formulas
Range	Formula
C3	C3	=IF(B3-A3<TIME(12,0,0),0,((B3-A3)-(TIME(19,0,0)-TIME(7,0,0)))*24)
C4	C4	=(MAX(TIME(7,0,0)-A3,0)+MAX(B3-TIME(19,0,0),0))*24

 

[uswyne]

Hi, I got what I wanted but now I need to answer 1:30 instead of 1.5.
can u help me out?

 

[Sufiyan97]

just remove *24

 

[uswyne]

just remove *24

Thank u very much

 

[Sufiyan97]

You're welcome!