PPRuNe Forums - View Single Post - Log book on a spread sheet
View Single Post
Old 11th Sep 2014, 07:19
  #10 (permalink)  
ExGrunt
 
Join Date: Mar 2003
Location: England
Posts: 286
Likes: 0
Received 0 Likes on 0 Posts
I confess that I had some help to get to this:


=if(I13<>ʺʺ,HOUR(I13)+ROUND(MINUTE(I13)/60,1)+0.2,ʺʺ)


where the +0.2 is the extra 12 minutes for taxi time.
Potentially, there are all sorts of risks with this formula, but without seeing the rest of the sheet it is difficult to assess what is going on. For example if I13 = 0 then the formula will produce 0.2, which I don't think that is what is expected. Equally, when mixing time and decimal values, 0.2 can be interpreted as 0.2 of a day (because excel stores dates and times as a decimal value) so 0.2 can be 4 hours 48 minutes.

One thing you should avoid is constants in formulas ie +0.2. it is good practice to put the value in a cell with a proper label. Some people like to use a named value. When giving names it is usual to capitalise parts of the name. This means that you can type in the name in lower case eg: taxitime and if Excel recognises it will convert it to the capitalised name eg TaxiTime. It is a quick visual check that the correcct name is being used. It also makes the formula a bit more understandable eg:

=if(I13<>ʺʺ,HOUR(I13)+ROUND(MINUTE(I13)/60,1)+TaxiTime,ʺʺ)

HTH

EG
ExGrunt is offline