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

# 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
1 Solution

IT 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

Commented:

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
``````

See attached example.
RunTimeCalc.xlsx
0

Author 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.