yourcontactsfriend
New Member
- Joined
- May 22, 2008
- Messages
- 2
Alright, I've been at this for a few days now and my master excel buddies haven't been able to figure this one out either. So they referred me to Mr. Excel...
Problem:
I'm calculating a variety of cells that have a format of [h]:mm:ss. I'm using this format as it displays the information correctly in the cell and using a normal h:mm:ss format doesn't work. I'm calculating these cells using a =SUMIF(M34:M50000,">00:05:00") formula to sum up the amount of time that occurs that is greater than 5 minutes. The problem occurs when I try to make a calculation or convert this type of formatted cell into a normal number format so that I can make calculations off of it. For example, if the sumif formula returns data "01:30:00" I need to convert that cell into minutes (remember the format is [h]:mm:ss (with brackets)) so that it returns "90" minutes so that I can do another calculation off of the "90" minutes.
The one conversion that got the closest was a =(E15-INT(E15))*1440 convert but it ignores the hour section when their are brackets around the hour in the format and if I remove the brackets the time is not displayed correctly.
Hopefully this is enough explaination and its understandable. Any help with this would be greatly appreciated.
Thanks.
yourcontactsfriend
Problem:
I'm calculating a variety of cells that have a format of [h]:mm:ss. I'm using this format as it displays the information correctly in the cell and using a normal h:mm:ss format doesn't work. I'm calculating these cells using a =SUMIF(M34:M50000,">00:05:00") formula to sum up the amount of time that occurs that is greater than 5 minutes. The problem occurs when I try to make a calculation or convert this type of formatted cell into a normal number format so that I can make calculations off of it. For example, if the sumif formula returns data "01:30:00" I need to convert that cell into minutes (remember the format is [h]:mm:ss (with brackets)) so that it returns "90" minutes so that I can do another calculation off of the "90" minutes.
The one conversion that got the closest was a =(E15-INT(E15))*1440 convert but it ignores the hour section when their are brackets around the hour in the format and if I remove the brackets the time is not displayed correctly.
Hopefully this is enough explaination and its understandable. Any help with this would be greatly appreciated.
Thanks.
yourcontactsfriend
