Need Help for making time difference

[manisha_waghole]

Hello Sir,

I have In time, Out time, and Service window, I have to calculate extra hours worked. i can calculate for one day but if person comes on 1-Jan-24 at 4:00 PM and go 2-Jan-24 at 2:00 AM how can i calculate.
Service window -9 Hr
In time- 1-Jan-24 at 4:00 PM
Out time - 2-Jan-24 at 2:00 AM
We mention only one date of coming..
Plz suggest ...

below is format -

Employee Name	Date	In Time	Out Time	Total Hours	Extra Hours Worked
Manisha	1-Jan-24	4:00:00 PM	2:00:00 AM	?	?

 

[Marc L]

Hello, according to Excel rules with the bad idea to separate date & time then an additional operation is necessary.​

The formula must test if Out Time is inferior than In Time in order to add 1 as according to Excel Date 1 is a day,​

like in this case Total Hours is 1 + Out Time - In Time : =(D2<C2)+D2-C2 …​

​

 

[Marc L]

​

Extra Hours Worked formula : =(E2-TIME(9,0,0))*(E2>TIME(9,0,0)) …​

​

 

[pecoflyer]

It's always best (especially with date/time problems) to post an excel sheet, not a picture. Thx