Change Forum... Civil Aviation Travel, Polls & Prefs Tech/Ops Aviation Hobby Aviation Photography Photography Feedback Trip Reports Military Av & Space Non-Aviation Site Related LIVE Chat My Starred Topics | Profile | New Topic | Forum Index | Help | Search
 Question For The MS Excel Gurus: Formula Help?
 FLY2HMO From , joined Dec 1969, posts, RR: Posted Thu Feb 18 2010 14:41:51 UTC (5 years 1 month 1 week 15 hours ago) and read 3247 times:

 So I'm building a spreadsheet where I need to calculate the operating cost per hour per route for a plane. So in simple terms the formula would be column A (duration of flight) times column B (cost to operate aircraft per hour) = cost for flying the route The problem is that I can't figure out for the life of me (short of converting hours to decimal format) how to make excel multiply the time in regular time format correctly to get the correct results. A sample formula that I'm trying to do: 1:30hrs x \$850= \$38 (according to excel, WTF?!?) The A column is selected to be in time format. If i change the hours to normal general decimal format then excel gives me the right answer: 1.5hrs x \$850 = \$1275 I've searched like crazy on the help system and on google and cant find an answer. I need to use non decimal format times for a presentation. Any ideas? Using office 2007 by the way[Edited 2010-02-18 14:42:49]
 Phoenix9 From Canada, joined Aug 2007, 2546 posts, RR: 9 Reply 1, posted Thu Feb 18 2010 14:53:04 UTC (5 years 1 month 1 week 14 hours ago) and read 3237 times:

 Quoting FLY2HMO (Thread starter):I need to use non decimal format times for a presentation. Any ideas?

Enter the time in minutes instead of hours, you won't need to enter decimals

This is an indirect method: create another column with time in decimals and use that in your formula. When doing the presentation, just shrink the column to hide it or use 'white' as the text colour

Sorry couldn't help more.

 Life only makes sense when you look at it backwards.
 2H4 From United States of America, joined Oct 2004, 8956 posts, RR: 59 Reply 2, posted Thu Feb 18 2010 14:57:59 UTC (5 years 1 month 1 week 14 hours ago) and read 3235 times: AIRLINERS.NET CREWDATABASE EDITOR

 Quoting Phoenix9 (Reply 1):just shrink the column to hide it

You can also highlight the columns you'd like to hide, right click up on the letter of the column, and select 'hide'.

 Intentionally Left Blank
 Phoenix9 From Canada, joined Aug 2007, 2546 posts, RR: 9 Reply 3, posted Thu Feb 18 2010 15:02:42 UTC (5 years 1 month 1 week 14 hours ago) and read 3228 times:

 Quoting 2H4 (Reply 2):You can also highlight the columns you'd like to hide, right click up on the letter of the column, and select 'hide'.

Oh yes. D'oh! Forgot that excel has that option.

 Life only makes sense when you look at it backwards.
 casInterest From United States of America, joined Feb 2005, 5078 posts, RR: 3 Reply 4, posted Thu Feb 18 2010 15:13:19 UTC (5 years 1 month 1 week 14 hours ago) and read 3220 times:

 You forget that you are using two different units values to multiply into \$/hour. if you truly want \$ per hour as your multiplier you need too have the time entered as hours. Think of this as a modulus operation on the true number. Hours of the timestamp+Minutes of the Timestamp converted to hours by dividing by 60 I ran a sample listed below. I offer up the following: Where A is your Time format in X:X and B is your Cash =(HOUR(\$A1)+(MINUTE(\$A1)/60))*\$B1 1:30 \$850.00 \$1,275.00 4:30 \$850.00 \$3,825.00 2:30 \$850.00 \$2,125.00 3:15 \$850.00 \$2,762.50 7:30 \$850.00 \$6,375.00
 Older than I just was ,and younger than I will soo be.
 Dreadnought From United States of America, joined Feb 2008, 9154 posts, RR: 24 Reply 5, posted Thu Feb 18 2010 15:15:30 UTC (5 years 1 month 1 week 14 hours ago) and read 3221 times:

 Excel uses a time system based on day units, where 1 day =1, 1 hour = 0.041666667, 1 minute = 0.000694444 etc. If you type in a cell a time in a recognizable format, like 1:30, excel automatically converts it to it's own system, which would be 0.0625. The way to get around that is not to use such formats, and put in simply the units you want, like hours. 1.5 instead of 1:30. You can do dozens of other things as well if you want to experiment with text parsers etc.
 Veni Vidi Castratavi Illegitimos
 Petertenthije From Netherlands, joined Jul 2001, 3437 posts, RR: 11 Reply 6, posted Thu Feb 18 2010 15:17:51 UTC (5 years 1 month 1 week 14 hours ago) and read 3218 times:

 I use the Dutch version, so the exact term might be slight different. Say you got your begin time in cell A1, end time in B1 and the total time in C1 (=B1-A1). You have two options, one simple one a bit longer: A1 = 10:00 B1 = 12:30 C1 = 2:30 D1, add funtion "=HOUR(C1)", result will be "2", multiply this by the price per hour so you get "=HOUR(C1)*850". You'll get 2*850 = 1700. E1, add funtion "=MINUTE(C1)", result will be "30", divide this by 60 (minutes) and multiply by the price per hour so you get "=(HOUR(C1)/60)*850". You'll get 0,5*850 = 425. F1, add function =D1+F1". You'll get 1700 + 425 = 2125. Or you can combine the function in cell D1 and E1 into one big function: "=(HOUR(C1)+(MINUTE(C1)/60))*850". If you really want to make your sheet future proof you might want to consider putting the price per hour in another cell and letting the formula refer to that cell. If the price per hour changes, you only need to change the input cell and not all functions.[Edited 2010-02-18 15:19:00]
 Attamottamotta!
 FLY2HMO From , joined Dec 1969, posts, RR: Reply 7, posted Thu Feb 18 2010 16:56:33 UTC (5 years 1 month 1 week 12 hours ago) and read 3183 times:

Oh man I'm drinking out of a firehose

(I just realized the implications of that, therefore...) *that's what she said*

But I'll experiment with the suggestions.

 Quoting Phoenix9 (Reply 1):Enter the time in minutes instead of hours, you won't need to enter decimals

LOL sure smarty pants then I have to use operating cost per minute in the rest of the cells.

 Quoting Dreadnought (Reply 5):The way to get around that is not to use such formats, and put in simply the units you want, like hours. 1.5 instead of 1:30.

I'll do that for now just to get some useful numbers

That's probably what I'll end up using for the presentation.

Thanks ya'll!

 Phoenix9 From Canada, joined Aug 2007, 2546 posts, RR: 9 Reply 8, posted Thu Feb 18 2010 17:01:57 UTC (5 years 1 month 1 week 12 hours ago) and read 3179 times:

 Quoting FLY2HMO (Reply 7):LOL sure smarty pants then I have to use operating cost per minute in the rest of the cells.

of course. then you just make another hidden cell that has the operating cost/minute and use it in your calculations

 Life only makes sense when you look at it backwards.
 Top Of Page Change Forum... Civil Aviation Travel, Polls & Prefs Tech/Ops Aviation Hobby Aviation Photography Photography Feedback Trip Reports Military Av & Space Non-Aviation Site Related LIVE Chat Forum Index

This topic is archived and can not be replied to any more.

Printer friendly format

 Similar topics: More similar topics...
Question For The Railfans posted Mon Aug 25 2008 17:39:31 by Goldenshield
A Question For The Cyclists... (Bike Choice) posted Sun Aug 10 2008 15:07:33 by Lincoln
Question For The Day posted Mon Nov 5 2007 08:14:28 by Kmh1956
Another (Virus) Question For The Computer X-Sperts posted Thu Nov 1 2007 10:44:24 by TSV
A Question For The English.... posted Sun Oct 14 2007 14:30:48 by GKirk
A Simple Question For The American Readers posted Wed Oct 10 2007 05:42:57 by Farcry
Question For PC Home Network Gurus posted Sat Jun 30 2007 16:56:59 by 777DadandJr
Question For The Brits - 80's Singers posted Sat May 12 2007 07:45:02 by Diamond
A Question For The Family Guy Fans posted Tue May 1 2007 20:40:01 by CaptOveur
Question For The French posted Wed Feb 7 2007 15:15:08 by Dougloid