CODEWITHSUNDEEP
exceldatetimeexcel-formula
Mike
Mike

Reputation: 379

How to get hour and minute a time cell when it is more than 24:00 in EXCEL

When summing multiple excel cells which contain time durations (for instance the duration of the time an employee is present in the office for each day of a month), the sum gets bigger than 24:00 hours and we have to select a specific Type of Time Category in the Number tab in Format Cell (Normally it is named 37:30:55 for clarification). It works fine. But using the typical HOUR and MINUTE functions does not give the right HOUR part and MINUTE part of that summation.

For example in the image attached, the sum of durations is 126:06:00 and we expect the output 126 and 6 when using HOUR(D33) and MINUTE(D33) respectively. The MINUTE function works fine, but seems the HOUR function always gets confused.

enter image description here

Is there any other function that can help solve this issue?

Upvotes: 0

Views: 121

Answers (1)

Scott Craner
Scott Craner

Reputation: 152450

As date/timeis just a double, use this formula:

=INT(A1*24)

enter image description here

enter image description here

Upvotes: 3

Related Questions