# Thread: Need help with Formulae

1. ## Need help with Formulae

(Edited by HansV to make URL clickable - see <!help=19>Help 19<!/help>)

Hello,

I need my fellow WOPR Friend's help again. As per the attached XL file, I want to fill out Availability Values (e.g. for a Server) for a 28 day month i.e. 28x24= 672 hours.
Thus for example 1% of 672 hours = 6.72 hours and based upon this, the:
EQUIVALENT DOWNTIME = 665h 16m 47s (Time Notation) and 665.28 h (Decimal Notation).
The CORRESPONDING UPTIME = 6h 43m 11s (Time Notation) and 6.72 h (Decimal Notation).
The website http://www.springfrog.com/converter/decimal-time.htm has a very useful decimal/time converter. But I want to get Excel to do the same thing. Please advise me on how I can do this?! Thanks again WOPRs

2. ## Re: Need help with Formulae

See attached.

3. ## Re: Need help with Formulae

Excel can format it directly.
Excel stores dates and times as days. So take
EQUIVALENT DOWNTIME = 28 (days) *99%=27.72 days
CORRESPONDING UPTIME = 28(days) * 1% = 0.28 days

Now just format them to display the desired "result"
Format -cells - custom:
<pre>[h]"h" m"m" s"s" </pre>

Steve

4. ## Re: Need help with Formulae

Thanks again guys.
So just for me to be lucid on this

5. ## Re: Need help with Formulae

Dear Steve,

I want to try your solution but I may need a bit of help here.

6. ## Re: Need help with Formulae

Availability for the month = (No. of Days in that Month) * (Availability Percentage Level).
The hours should not enter into it. You will calculate the time in days. Excel "builtin" routine will do the converting to whatever format you desire:
[h]"h" m"m" s"s" 10h 15m 3s
or
[h]:mm:ss.00 876:54:32.11

or whatever.
Steve

7. ## Re: Need help with Formulae

There is no file attached.

Make sure you
<hr><font color=red>* DO This LAST - AFTER Previewing and AFTER Spell-Checking - OR you will lose your attachment! * </font color=red><hr>

Steve

8. ## Re: Need help with Formulae

Here's the file sorry.

Bob.

9. ## Re: Need help with Formulae

If you want the 3 (hrs/mins/secs) separate just use Hans' formulae.

If you want 1 value and have it displayed via a custom format use the formula:
<pre>=F5/24</pre>

Which takes your hours and converts to days
Then format as I described earlier

You could also just use the percentage directly (as mentioned earlier)
<pre>=B5*28</pre>

Note: I assume you got the error since you copied the F5 formula to C5 and the relative reference to a column 4 columns to right is not valid, since you are at the 2nd column, giving the #err in the formula.

Steve

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•