# Calculating late hours exceeding the 24 hours

#### Juniad

##### Active Member
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.

#### Attachments

• Late Arrival.xlsx
11.8 KB · Views: 5
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.

#### Attachments

• Late Arrival.xlsx
11.8 KB · Views: 3
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
Code:
``=SUMPRODUCT(--((MOD(COLUMN(E3:AH3),2)=1)),(E3:AH3))-(COLUMNS(E:AH)*8/48)``

#### Attachments

• Late Arrival.xlsx
11.8 KB · Views: 6
Last edited:
Thanks for your help and suggestion @pecoflyer

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.

#### Attachments

• Late Arrival (1).xlsx
13.1 KB · Views: 7

#### 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

Try
Code:
``=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)

Thank you everyone for the help. Problem Solved <3