Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 347
  • Last Modified:

calculate hours with excel

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
Inframap
Asked:
Inframap
1 Solution
 
helpfinderIT ConsultantCommented:
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
 
frankhelkCommented:
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
 
InframapAuthor Commented:
great thank you
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now