• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 346
  • 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

Featured Post

[Webinar] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

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