Help with Excel
Thread Starter
Join Date: Oct 2004
Location: Everywhere
Posts: 190
Likes: 0
Received 0 Likes
on
0 Posts
Help with Excel
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
So if anyone out there can help, I would greately appreciate the advice
Many Thanks,
S.C.
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
So if anyone out there can help, I would greately appreciate the advice
Many Thanks,
S.C.
ανώνυμος
Join Date: Feb 2004
Location: Perth
Posts: 111
Likes: 0
Received 0 Likes
on
0 Posts
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
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
Last edited by R4+Z; 16th Dec 2004 at 03:18.
Join Date: Nov 2000
Location: Home
Posts: 903
Likes: 0
Received 0 Likes
on
0 Posts
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
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
Thread Starter
Join Date: Oct 2004
Location: Everywhere
Posts: 190
Likes: 0
Received 0 Likes
on
0 Posts
Brilliant Stuff
Thanks for all the help with this, i've been able to set it up with a seperate summary page adding all the years.
Now I look forward to writing up a few thousand lines
Should keep me busy,
Thanks again for the help, much appreciated,
S.C.
Thanks for all the help with this, i've been able to set it up with a seperate summary page adding all the years.
Now I look forward to writing up a few thousand lines
Should keep me busy,
Thanks again for the help, much appreciated,
S.C.
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.
=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.
Last edited by Tinstaafl; 17th Dec 2004 at 22:20.
