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.

 

[gapiesco]

At the risk of sounding ignorant, can you give me an example of how that would work/look with either of time examples above. Never dealt with datetime values (per say). Thanks.

 

[Zathras]

I still can't quite grasp what exactly you are entering in your worksheets.

For example, if you type in 37:30:55 that is what you will see in the cell, but if you look in the editing window above the grid you should see 1/1/1900 1:30:55 PM. If you now go to an empty cell and type =AH12 you will again see 37:30:55 BUT now change the format of that cell to General and you should see 1.56313657 (more or less depending on the column width). This represents slightly more than one and a half days which corresponds to slightly more than 36 hours. Dreamboat alluded to that when the formula indicates AH12 times number of hours in a day (24) times number of minutes in an hour (60).

But I remain mystified as to how you achieve precision down to the second without using date or time functions.

Your initial post gave the formula:
=(((HOUR(AH12)*60)+MINUTE(AH12)+SECOND(AH12)/60)/43200)-100

The problem with this formula is the HOUR(AH12) part. This gives only 13 hours when you expected 37 -- off by 24 hours.

All 37 hours are counted with this fomula:
=(AH12/30)-100

To prove it consider this modification to your formula:
=((((24+HOUR(AH12))*60)+MINUTE(AH12)+SECOND(AH12)/60)/43200)-100

By adding back the missing 24 hours before multiplying by 60, the results are identical = -99.94789545.

It may be that all of your problems stem from misuse of the HOUR function.

 

[jdol]

The solution for your problem is quite simple.

If you want your percentage in a cell, give this cell the percentage-format and the formula: '=AH12/30' (or 31 in p.e. january, or 28 in february). That's all!!

 

[gapiesco]

Zathras, I definitely run into the 24 hour delemma. Messaging the formulas works but doesn't help for this reason. I have fifty spreadsheets with anywhere from 10 to 25 entries. Each of those entries can vary greatly. One month line three may exceed 24 hour, the next month it may be zero. If I had to change the formula to all the cells that exceed 24 hours across all 500+ possible entries, it might be quicker to do it with a calculator.

That why I was considering a convert function, but see my thread as to problems encounter there. Thanks.

 

[gapiesco]

Idol, I'm not dividing by number of days in a month, but the number of minutes.

 

[Zathras]

Please, please, stop a minute and listen to what jdol, xlhelp, dreamboat and I are trying to communicate to you...

You do not need to use HOUR, MINUTE, and SECOND formulas to get what you need.

My modification to your formula was NOT a suggestion as to how to proceed, it was simply a demonstration of the fact that such a modification is needed to handle the >24 hour situation. It is identical in results with dividing the raw datetime number by 30 to get the ratio you want.

You don't have to "change the formula to all the cells that exceed 24 hours across all 500+ possible entries" -- You should change ALL the formulas to the simpler format jdol, xlhelp, dreamboat and I have indicated.

Look in the Excel help (Contents and Index) under "Now" and study the examples at the bottom.

 

[gapiesco]

I apologize to all for my misunderstandings. I did talk with xhelp on the phone this morning and he was able to clarify and complete a workable formula which includes the message you've been trying to get across. We applied the following: =(AH11*24*60/43200-1)*100. This seems to be working across the board.

I'm very appreciative of your time and efforts. I'm rapidly becoming a fan of Tek-Tips and I hope as I visit more frequently, I'll read more attentively. THANKS.

 

[Dreamboat]

Wow! xlhelp never called me!


That's great, isn't it? Woohoo! Anne Troy

http://www.TheOfficeExperts.com

 

[xlhelp]

I called you Marilyn Monroe the other day

 

[jdol]

gapiesco,

I understood you're dividing by minutes. But why?
Your goal is a percentage of a month; my formula deals with minutes and seconds too, so y'll always get the exact percentage. Try it!

 

[Dreamboat]

LOL xlhelp! Anne Troy

http://www.TheOfficeExperts.com