Excel querie

[Flying Mushroom]

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

[Rollingthunder]

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.

[Flying Mushroom]

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

[Rollingthunder]

OK, Set up three columns.
Hours, Minutes, Decimals.
In decimal column insert as an example =B2/60+a2
And paste onwards.

[P.Pilcher]

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.

[rotorcraig]

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

[G7SWZ]

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

[WideBodiedEng]

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!

[Flying Mushroom]

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:

[Terraplaneblues]

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.

[mazzy1026]

Typing in:

=Now()

Into a blank, unformatted cell will give you the present date and time upon pressing the return key :D