Excel querie
Hopefully this will be a simple question to all you masters of the mystic Excel (as you may gather I'm totally lost with this program)...
Ive managed to create a basic invoicing system that adds Flight time and I'd like it to multiply this by an hourly rate, but I'm struggling. Is there a simple way of formatting the cells to convert the minutes to a decimal or is it more complicated than this? If possible I'd like to keep time in one cell containing hh:mm, rather than having two cells for hours and minutes respectively (although if seperate cells make the solution easier then so be it). I hope this makes sense? If anyone can help me, I would be very grateful Thanks Flying Mushroom :D |
Minutes - Decimal
1- 0.017 2- 0.033 3- 0.050 4- 0.067 5- 0.083 6- 0.100 7- 0.117 8- 0.133 9- 0.150 10- 0.167 11- 0.183 12- 0.200 13- 0.217 14- 0.233 15- 0.250 16- 0.267 17- 0.283 18- 0.300 19- 0.317 20- 0.333 21- 0.350 22- 0.367 23- 0.383 24- 0.400 25- 0.417 26- 0.433 27- 0.450 28- 0.467 29- 0.483 30- 0.500 31- 0.517 32- 0.533 33- 0.550 34- 0.567 35- 0.583 36- 0.600 37-- 0.617 38- 0.633 39- 0.650 40- 0.667 41- 0.683 42- 0.700 43- 0.717 44- 0.733 45- 0.750 46- 0.767 47- 0.783 48- 0.800 49- 0.817 50- 0.833 51- 0.850 52- 0.867 53- 0.883 54- 0.900 55- 0.917 56- 0.933 57- 0.950 58- 0.967 59- 0.983 60- 1.000 One way to do it. Print and use 'till you memorize it. You will. |
Thanks Rollingthunder,
this is exactly what I've been doing, but would like the program to calculate it automatically..... I guess Im just getting lazy :ok: Thanks for replying FM |
OK, Set up three columns.
Hours, Minutes, Decimals. In decimal column insert as an example =B2/60+a2 And paste onwards. |
There are probably other, simpler ways of doing this, but before excel I was using spreadsheets to keep the sums of my logbook accurate, and continued this way when excel became the spreadsheet to use. It is quite simple (I think!)
Hours and minutes are entered in cells using a decimal format, so two hours and fourty minutes is entered as 2.4 and let's say you entered it in cell A1. Now by use of the following formula, it is converted into real decimal hours, so you bung into another cell: =INT(A1)+(A1-INT(A1))/0.6 this should make this cell read 2.666667 which is the decimal hours of 2 hours 40 minutes. Now you can do what you like with this, multiply it by an hourly rate or add it to another similarly decimalised hours.minutes entry (as I do). Should you then wish to turn the answer into hours and minutes again in an hours.minutes format, the process is reversed by using the formula =INT(A1)+(A1-INT(A1))*0.6 where again cell A1 is the cell where the decimal hours which require their format converting reside. I expect that there is a much simpler way of doing this in excel, but it is the way I understand because I have always used it since the days of supercalc and DOS. It works for me! P.P. |
Try this...
1. Start a new spreadsheet 2. In cell A1 type the flight duration 00:48 3. In cell A2 type the hire rate (ex VAT) £150 4. In cell A3 type the formula =A1*24*B1*1.175 5. Change format of cell A3 to be Currency (example answer should be £141.00) That should get you on your way? RC |
You can use the time and date function in Excel to get to what you want.
Set up 5 columns headed as follows (or whatever you want to name them) Start Time: End Time: Actual Time: Day Rate: Time Charge: Enter in the first column you start time for example 09:55:00 Enter in the second column your end time for example 11:05:00 Enter in the third column the formula =(b2-a2) which means 11:05:00 less 09:55:00 This will give the answer 01:10:00 In the next calculation that answer is recognised as a fraction of a day. So if your hourly charge was £50.00 enter in the next column £1200.00 which amounts to (24*£50) In the next column enter the formula =(d2*c2) which means 01:10:00 times £1200 This will give you the answer £58.33 You need to format the cells in columns a, b, and c as "custom" and select from the options given "hh:mm:ss" You need to format the cells in columns d and e as "currency" Hope this gives you what you want. Regards G7SWZ |
Keep It Simple
The easiest method is P Pilcher's. The other methods require multiple keystrokes. eg 2 point 48 is easier to type than 2 colon 48 or 2 next column 48. I've used the integer system in log books for years and it's simple. Gotta admit it was a pilot who first suggested it to me, though!
|
Thanks everyone for your replies, its all tickety-boo now.
PP your method is great and simple, just what I was looking for. Thanks! :D Now here's one for you all.... I've searched through the 'Help' bit but can't find out how to label each entry with todays date. I've tried the NOW and TODAY commands, but they rewrite todays date onto previous saved entries (entered earlier in the week). I know it can be done as someone once showed me how (but the old brain cell has slept since then) Thanks for your help again, FM :ok: |
This is associated: paste this formula anywhere on a spreadsheet and in cell B12 (for instance) write 1230, the cell with the formula now has a value of 12:30 if formatted as time. (0.520833333333333 in reality)
It just saves the bother of writing colons:::::::::::::: =IF(B12>1,TIME(+ROUNDDOWN(B12/100,0),(+B12-(ROUNDDOWN(B12/100,0)*100)),0)," ") Found it on the web. Handy when you have to write hours & minutes in a lot. |
Typing in:
=Now() Into a blank, unformatted cell will give you the present date and time upon pressing the return key :D |
All times are GMT. The time now is 04:37. |
Copyright © 2024 MH Sub I, LLC dba Internet Brands. All rights reserved. Use of this site indicates your consent to the Terms of Use.