Solved

calculate hours with excel

Posted on 2013-01-19
3
287 Views
Last Modified: 2013-01-20
Hello

I have the following columns

Run Hours  | Startup Total | Downtime Total |   Operating Hours
24                        2:00                       1:35                    

I am trying to solve operating Hours

The problem is that Run Hours is formatted as a number (i can not change the format as this info comes from other worksheets as data entry)   Startup Total and Downtime Total are formatted as [h]:mm  and these totals come from other worksheets.   To change formating of any of these cells would require extensive rework

The operating hours should be 24 - 2:00 - 1:35 = 21:25 hours formatted as [h]:mm

How do i subtract the hours from the Number and get the right number of hours?
0
Comment
Question by:Inframap
3 Comments
 
LVL 19

Expert Comment

by:helpfinder
Comment Utility
if I do such a formula in excel (24-2:00-1:35) I get result as you want (20:25) - see attached excel sample.
Do you have it somehow else? If so, could you attach your file (or sample where it does not match)?
sample.xlsx
0
 
LVL 13

Accepted Solution

by:
frankhelk earned 500 total points
Comment Utility
The example is misleading ...

the number 24 is an integer, and excel internally represents time as a fraction of the day. Therefore any integer number instead of 24 would result in the same result:

24-2:00-1:35 = 20:25
23-2:00-1:35 = 20:25
22-2:00-1:35 = 20:25
and so on.

Unfortunately the 24 represents a full day either, so the first try to simply subtract pretends to work anyhow.

You got to use a little more magic to do the wanted trick - convert the number of hours into ecxel's own representaion (fraction of a day) by dividing it with 24.

OpHours = (RunHours / 24) - Startup - Downtime

Open in new window


See attached example.
RunTimeCalc.xlsx
0
 

Author Closing Comment

by:Inframap
Comment Utility
great thank you
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Recently Microsoft released a brand new function called CONCAT. It's supposed to replace its predecessor CONCATENATE. But how does it work? And what's new? In this article, we take a closer look at all of this - we even included an exercise file for…
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

772 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now