Calculating late hours exceeding the 24 hours

[Juniad]

Please find the attached file, facing issue to calculating the late hours if hours are exceeding from 24. Less than 24 hours formula result is perfect.

 

[GrandZeno]

As I am rookie, May be I am wrong...
what we want is sum total of hours, ex., 25:01:00 instead of 01:01:00,
please try with change in Format Cell(Time) (ex., 37:55:00 )...
I am also waiting for experts reply.

 

[pecoflyer]

Right click your cell - Format Cells - Number Tab - Select Custom - Type: scroll down to and select [h]:mm:ss - OK
If you don't need the seconds just enter manually [h]:mm in the Type bar
(BTW using merged cells for your dates is a bad idea and will get you into trouble for further analysis)
Also your formula can be shortened to

=SUMPRODUCT(--((MOD(COLUMN(E3:AH3),2)=1)),(E3:AH3))-(COLUMNS(E:AH)*8/48)

 

[Juniad]

Thanks for your help and suggestion @pecoflyer

 

[Juniad]

Hi @pecoflyer
Please have a look at the sheet I have added more data in next rows and while dragging the formula down its giving incorrect answer.

 

[vletm]

Juniad​

cell AI3 =SUMIFS(E3:AH3,E$1:AH$1,"In",E3:AH3,">"&TIME(8,0,0))-COUNTIFS(E3:AH3,">" & TIME(8,0,0),E$1:AH$1,"In")*TIME(8,0,0)
and copy down

 

[pecoflyer]

Try

=SUMPRODUCT(--((MOD(COLUMN(E3:AH3);2)=1)),(E3:AH3))-((COLUMNS(E:AH)-2*COUNTIF(E3:AH3,""))*8/48)

( your expected result in row 4 does not seem correct)

 

[Juniad]

Thank you everyone for the help. Problem Solved <3