Performing calculation on time

[gapiesco]

I need help with following formula:
=(((HOUR(AH12)*60)+MINUTE(AH12)+SECOND(AH12)/60)/43200)-100

The column AH is formatted for time (37:30:55); the result column is number with 2 decimal places. I'm trying to calculate the percentage of hours available in a given month - in this case a 30-day month equalling 43,200 minutes. The formula seems to work fine until the hours exceed single digit. It doesn't work when hours reach 10+. In some instances I'm trying to calculate percentages on 100+ hours in a month. Is there something I can do to the HOUR in the formula so that it recognizes 100+ hours.

 

[Dreamboat]

Please, in the formula bar, what are the values in AH12? Anne Troy

http://www.TheOfficeExperts.com

 

[gapiesco]

For example, I have a total time of 129 hrs, 35 mins, 50 secs (129:35:50) that something was broke for the month of November (43,200 minutes). That would hopefully equate to 99.820% available. Right now the formula returns 99.9867. Thanks for trying to help.

 

[Dreamboat]

Where is that 129:35:50 coming from? Are you calculating it within Excel or do you get this data from some other program?

(Hee hee, I'd love to see this workbook. Anne@TheOfficeExperts.com) Anne Troy

http://www.TheOfficeExperts.com

 

[gapiesco]

Yes, I'm tracking multiple downtimes for pieces of equipment on one spreadsheet (totalling for the month). 129:35:50 represent total downtime for eight separate entries. You don't want to see the workbook (50 separate sheets, each 36 columns, 30 rows)! I carry the total columns forward to a summary sheet in which I'm trying to use this formula. Hope that helps

 

[Dreamboat]

Ok...

So 129:35:50 is NOT what is in cell AH12, it's actually a formula? That's why I asked what's in the formula bar... Anne Troy

http://www.TheOfficeExperts.com

 

[gapiesco]

I'm sorry. AH12 is actually the formula (HON!AH17). Some additional information too. That column on worksheet is also formatted as time 37:30:55.

 

[Dreamboat]

Right.

Select that cell and go to the format, click on custom format. What's it say?

You'll need something like [h]:mm:ss

But why format it as time there? Why not format it as number/decimal there already? If you're down to the seconds, you'll want like 6 decimals, I think.

By the way, if you have Winzip, I'm still perfectly willing to take a look at the workbook. Anne Troy

http://www.TheOfficeExperts.com

 

[xlhelp]

If you multiply the cell as follows:

=AH12*24*60

and then format the cell to General, you would get your 2250.92 minutes

 

[Dreamboat]

So if you take:

=(AH12*24*60)/43200

you should get your percentage. Anne Troy

http://www.TheOfficeExperts.com

 

[gapiesco]

I've tried the [h]:mm:ss format, didn't work. Don't see how that formula can work. Every drop is a different duration; may have one per month, may have 50.

 

[Dreamboat]

But I thought that right now, all you're trying to do is find the total down time % per month?? Anne Troy

http://www.TheOfficeExperts.com

 

[gapiesco]

No, I have the total downtime 129:xx:xx. I'm trying to get the percentage that represents of all available minutes in a given month. Attempting to send you a copy of spreadsheet.

 

[Dreamboat]

Interesting. Did you try 120:00:00? The formula does NOTHING. Working on it. Anne Troy

http://www.TheOfficeExperts.com

 

[Dreamboat]

Ok. I changed hour HOUR, MINUTE, SECOND, to just:

=Hour(D3) and it returns 9, so....

We're working on it.
I sent a copy to xlhelp.

Hope that's okay with you. Anne Troy

http://www.TheOfficeExperts.com

 

[gapiesco]

I've figured out a work around, but it requires two separate calculations. I used =(((HOUR(AH3)*60)+MINUTE(AH3)+SECOND(AH3)/60)) to get a total minute column (using number format & 2 decimal places). Then I apply the formula =100-((+AI12/43200)*100)to that new column. Of course I'll have to add an additional 12 columns to my spreadsheets (50 of them)... Hopefully we can get it to one formula. I have a fall back now that you've got my brain racking. Thanks.

 

[Dreamboat]

LOL
Add columns to all spreadsheets at one time (as long as it's the SAME columns), by grouping them first....then insert the columns, insert formulas, etc....all at once. Don't forget to ungroup! Anne Troy

http://www.TheOfficeExperts.com

 

[gapiesco]

Just ran into another SNAFU. That process works great until you exceed 23:59:59. At 24 hrs, it zeros out again. Still need help. Is there any way custom or otherwise to circumvent the 24 hour clock? Thanks.

 

[Zathras]

At the risk of muddying up the waters, have you considered capturing the down time as a datetime value which is really just a decimal value. Then the difference between Now() and the frozen downtime is the number of days between the two points with the decimal part reflecting a fraction of a day. Multiply this by 24 to get hours. Multiply again by 60 to get minutes. Divide by 43,200 or whatever. No muss, no fuss.

You could of course just take the difference between the timestamps and divide by 30 and get the answer you want directly.

What am I missing?

 

[gapiesco]

Another thought, when I convert say 29:32:24 [=convert(ah18,"hr","mn&quot]it correct calculates minutes, secs to 1772:24 but displays 1772:24:00. Can't seem to get that cell to translate to 1772.4 to apply second calculation.