Solved

EXCEL: FORMULA FOR TIME CALCULATION HH.MM

Posted on 2011-10-13
Medium Priority
343 Views
Experts,

Does anyone know of a way to sum up the duration of two time periods.  I would like to show, hh.mm.

Here is the data:
EVENTSTARTTIME      EVENTENDTIME
Oct 03, 2011 10:59:00 AM      Oct 03, 2011 11:34:48 AM
Oct 03, 2011 02:30:00 PM      Oct 03, 2011 02:55:00 PM

I was using
=ROUND((E2-D2)*1440,0) with gives me 36 hrs I would like 36.XX
0
Question by:Maliki Hassani
• 5
• 3

LVL 34

Accepted Solution

Rob Henson earned 2000 total points
ID: 36963882
Is there a particular reason you need it in decimal hours, would hh:mm be OK.

If you use Custom Format [h]:mm it will ignore the fact that it goes over 24 hours and show total hours rather than allowing for days and hours.

Thanks
Rob H
0

Author Comment

ID: 36963890
I should have wrote 36:xx
0

Author Comment

ID: 36963898
When I format the cell to show hh:mm  I get 864:00
0

Author Comment

ID: 36963910
Think this will work
=TEXT(E2-D2,"h:mm:ss")
0

LVL 34

Assisted Solution

Rob Henson earned 2000 total points
ID: 36963938
The two samples you give above give differences of only 36(ish) & 25 Minutes.

Formatted as time this should show 00:35:48 and 00:25:00.

Why are you expecting it to be 36 hours?

Thanks
Rob H
0

Author Comment

ID: 36963956
Perfect thanks robbenson!
0

LVL 34

Expert Comment

ID: 36963962
Should have also said, need to include the square brackets around the hh to get hours to keep going when over 24 and not go back to zero as a clock would.

Thanks
Rob H
0

LVL 50

Expert Comment

ID: 36964068
unless you have a specific reason then using TEXT function is probably not a good idea - just subtract like

=E2-D2

....then format as Rob suggests

regards, barry
0

Author Comment

ID: 36964080
Good point!
0

Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
Suggested Courses
Course of the Month16 days, 18 hours left to enroll