?
Solved

EXCEL: FORMULA FOR TIME CALCULATION HH.MM

Posted on 2011-10-13
9
Medium Priority
?
343 Views
Last Modified: 2012-05-12
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
Comment
Question by:Maliki Hassani
  • 5
  • 3
9 Comments
 
LVL 34

Accepted Solution

by:
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

by:Maliki Hassani
ID: 36963890
I should have wrote 36:xx
0
 

Author Comment

by:Maliki Hassani
ID: 36963898
When I format the cell to show hh:mm  I get 864:00
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 

Author Comment

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

Assisted Solution

by:Rob Henson
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

by:Maliki Hassani
ID: 36963956
Perfect thanks robbenson!
0
 
LVL 34

Expert Comment

by:Rob Henson
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

by:barry houdini
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

by:Maliki Hassani
ID: 36964080
Good point!
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

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…

862 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question