Help with Excel

[Sky_Captain]

Looking for some help and advice in the use of Microsoft Excel. Have the Office 2003 version and I want to save to my laptop my logbook. I have created a page with all the catagories, and formatted the cells with my hours in time cells using "hh:mm". I tried using the autosum after 3 lines to see if it would add time (eg: 01:10+02:00+01:00) and it worked fine, giving me the proper answer

After filling in about 25 lines I re-tried the autosum, but the answer made no sense. what should have been around 50 hours, came out as 03:10:confused:

So if anyone out there can help, I would greately appreciate the advice:ok:

Many Thanks,

S.C.

[R4+Z]

Sky_Captain

Adding time in excell is not that straight forward as what you see in the cell is not what it is actually working with. Excell works with decimal time and as soon as you go over 24 hrs it exceeds the number the format can display so it reverts to 0 for the 24 and displays the remainder.

one way would be to format a cell to time and then the cell next to it you can format as general and make it equal the cell formatted as time. by entering times in the first cell you can see what values are being worked with and you can then work out the formula from there.

If I get time I will have a play and get back to you, assuming nobody else answers this first.

R4

Edited to add

Doh!

You don’t want to manipulate the figures you just want to add them up and have them display correctly.

In the cell format, use custom and instead of h:mm use [h]:mm (must be square brackets) and you should achieve the desired result.

R4

[Engineer]

Custom format the flight time and running total cells as mentioned above i e [h]:mm ATD and ATA will be in hh:mm

If you want to subtract your ATA from ATD and these occur either side of midnight then you will have to set up a formula in cell C1

=IF(SIGN(A1-B1)=-1,A1-B1+1,A1-B1)

Where A1=ATD B1=ATA and C1 is the flight time

It will produce the correct answer if you fly in a day or over midnight Good luck :ok:

[Llademos]

Format the cell with the total as [h]:mm (use the 'Custom' format option). This will stop the hour going to zero after 24

[Sky_Captain]

Brilliant Stuff:D

Thanks for all the help with this, i've been able to set it up with a seperate summary page adding all the years. :ok:

Now I look forward to writing up a few thousand lines :eek:

Should keep me busy,

Thanks again for the help, much appreciated,

S.C. :ok:

[Tinstaafl]

Similarly to Engineer's formula:

=IF(B1<A1,B1+1,B1)-A1

where

A1 is departure time
B1 is arrival time.

Excel stores all times & dates in the format

aaaaaa.bbbbbb

where aaaaaa is whole days from an arbitrary starting point (~1 Jan 1900 or thereabouts), and bbbbb is a decimal fraction of a 24 hr 'day' that represents hours, mins & secs.

.5 would be 12 midday, .75 would be 1800 hrs, 1.25 would be the 1 day & 6 hrs after the arbitrary starting date etc.

Mathematically, when a subsequent time occurs after midnight from an initial time then it's really 'plus 1 day' as well as the hours/mins/secs. The problem comes from us using a short version of the date/time group that omits the day. *We* know that the arrival time is the next day and mentally allow for the extra 'day' by calculating time up to midnight + the time from midnight and summing the results, but the spreadsheet has no way of knowing - hence the 'plus 1' in the formula which presumes a smaller number means the arrival time must have crossed midnight.

You could choose to use a date/time group for all time entries then you wouldn't need the formula I gave. Bit of a pain for data entry though.