If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#11
|
|||
|
|||
At 12:00 12 December 2004, Mal wrote:
Hi does anyone know the formula for excel so it will add the minutes after the decimal and convert them to hours. format the cells to h:mm you can also format to hh:mm or even to hh:mm:ss when adding two cells is likely to lead to hours in excess of 9 format [hh]:mm otherwise the sum will go wrong enter the times as 1:27 (1 hour 27 min) best rgrds Chris |
#12
|
|||
|
|||
Chris Kaminski wrote:
At 12:00 12 December 2004, Mal wrote: Hi does anyone know the formula for excel so it will add the minutes after the decimal and convert them to hours. format the cells to h:mm you can also format to hh:mm or even to hh:mm:ss when adding two cells is likely to lead to hours in excess of 9 format [hh]:mm otherwise the sum will go wrong enter the times as 1:27 (1 hour 27 min) best rgrds Chris What is VERY annoying with that solution is that you cannot enter flights less than one hour by simply writing the number of minutes. Eg. for a 23 minute flight you actually _have_ to write 0:27 which means you have to press four keys instead of two. CV |
#13
|
|||
|
|||
CV wrote:
Eg. for a 23 minute flight you actually _have_ to write 0:27 which means you have to press four keys oops, I meant 0:23 of course instead of two. CV |
#14
|
|||
|
|||
say hour:mins in A1, put B1=INT(A1) (gives hours in B1), C1=(A1-B1)*100 (gives minutes in C1) Yep, that would do the job for one single entry. However it would not allow you to add two or more entries to make a total. Yes - but I was putting over the principal. I assume that the list of times is in (say) column A. Set dummy columns B and C to carry the split times - copy the formula down as far as necessay, add and revert back concatenated time to bottom of column A (using the contraction descibed earlier). Keith |
#15
|
|||
|
|||
"Mal" wrote in message ... Hi does anyone know the formula for excel so it will add the minutes after the decimal and convert them to hours. One way is to put hours in one cell and minutes in the other and then convert all to minutes in a non-displayed cell [(hours X 60) + minutes]. At the bottom of the page you add together all of the minutes and then convert back to hours and minutes by using the interger (INT) function. To get the hours, use the formula [Int (minutes/60)] =hours. To recover the fraction of an hour in minutes, multiply the hours (from the previous formula) times 60 and then subtract from the minutes. Clear as mud? Vaughn Need to check my log book times and make a database off information types flown etc. EG 1.33 1.56 =2.89 Needs to say 60 mins is one hour so 1.33 1.56 =3.29 ??????????????? =SUM(E1:E23) =IF(A31000,TIMEVALUE(LEFT(A3,1)&":"&RIGHT(A3,2)),T IMEVALUE(LEFT(A3,2)&":"&RIGHT( A3,2))) The formatting of the cells in column B is HH:MM. Thanks Mal |
#16
|
|||
|
|||
Why not format the cells to a time format thus-
Format - cells - time - 37:30:55 This will give time in hours minutes and seconds that can be added Sorry excell dont work like that. If you try to enter say 1hr 30min as 1.5 it trys to use it as a date function and displays 12:00:00 and if you try to add times it treats them also as dates such that 12:00:00 + 12:00:00 = 0:00:00. I use two columns and add one mod 60 etc. Peter. Excell does work like that! ie for a flight of 8hrs 57min you put 8:57 You have to use the format including seconds at the one for just hrs:min will give you a false reading at higher values. I have entered my logbook for over 600 flights using this method and it works for me. |
#17
|
|||
|
|||
"OscarCVox" wrote in message ... Why not format the cells to a time format thus- Format - cells - time - 37:30:55 This will give time in hours minutes and seconds that can be added Sorry excell dont work like that. If you try to enter say 1hr 30min as 1.5 it trys to use it as a date function and displays 12:00:00 and if you try to add times it treats them also as dates such that 12:00:00 + 12:00:00 = 0:00:00. I use two columns and add one mod 60 etc. Peter. Excell does work like that! ie for a flight of 8hrs 57min you put 8:57 You have to use the format including seconds at the one for just hrs:min will give you a false reading at higher values. I have entered my logbook for over 600 flights using this method and it works for me. OK try summing a few time that when added are greater than 24 hrs. Or put a time in of 45hrs 36mins and see what it does then. Peter |
#18
|
|||
|
|||
"Peter Seddon" wrote in message OK try summing a few time that when added are greater than 24 hrs. Or put a time in of 45hrs 36mins and see what it does then. Peter Peter, I have been watching this thread and thought I would see what happens on my computer using Excel as delivered in Office 2000. Using the format Category of "Time" and the Type "37:20:55" I entered 12:22, 14:38, 72:14 and the above mentioned 45:36. Summing this column gave an accurate total of 144:50:00. What version of Excel are you using? Wayne, http://www.soaridaho.com/ |
#19
|
|||
|
|||
Wayne Paul wrote:
Using the format Category of "Time" and the Type "37:20:55" I entered 12:22, 14:38, 72:14 and the above mentioned 45:36. Summing this column gave an accurate total of 144:50:00. What version of Excel are you using? I tried this in Excel 97 (I'm too cheap to upgrade). The key seems to be using the "37:30:55" time format in the Format Cells dialog. If any other time format is used, the values are interpreted as time of day, and wrap around at 24 hours. Marc |
#20
|
|||
|
|||
"Marc Ramsey" wrote in message . com... Wayne Paul wrote: Using the format Category of "Time" and the Type "37:20:55" I entered 12:22, 14:38, 72:14 and the above mentioned 45:36. Summing this column gave an accurate total of 144:50:00. What version of Excel are you using? I tried this in Excel 97 (I'm too cheap to upgrade). The key seems to be using the "37:30:55" time format in the Format Cells dialog. If any other time format is used, the values are interpreted as time of day, and wrap around at 24 hours. Marc I'm on excel 2003 and the 37:30:55 time format aint available to me. Peter. |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
millionaire on the Internet... in weeks! | Malcolm Austin | Soaring | 0 | November 5th 04 11:14 PM |
Excel formula for logbook | Wizard of Draws | Instrument Flight Rules | 11 | August 30th 04 12:55 AM |
"I Want To FLY!"-(Youth) My store to raise funds for flying lessons | Curtl33 | General Aviation | 7 | January 9th 04 11:35 PM |
AmeriFlight Crash | C J Campbell | Piloting | 5 | December 1st 03 02:13 PM |
AOPA Stall/Spin Study -- Stowell's Review (8,000 words) | Rich Stowell | Piloting | 25 | September 11th 03 01:27 PM |