Excel 2K - Caclulating a total time.

  • Thread starter Thread starter The Admiral
  • Start date Start date
T

The Admiral

Hi,

I'm stuck. I know very little about Excel but am trudging along. I've got a
sheet set up to track times spent flying. I've it to figure time in the air
just fine when I put in the start and stop times, but at the bottom I want it to
total the actual air time. My problem is that after it tops 24 hours it goes
back to zero. Is there a way to get it to count days as well? For instance
if I've got listings that indicate 5 eight hour flights, the total at the bottom
shows at 8. I'd like it to indicate 1 day-8 hours-0 minutes.

Thankz.


Anchor's aweigh,

The Admiral
(e-mail address removed)
 
Hi!

To stop your total from rolling back after 24hrs, format
the cell as [hh]:mm.

Confused about this, though:

If you have FIVE 8hr entries, wouldn't that be:

1 day 16 hours ?

Biff
 
Hi!

To stop your total from rolling back after 24hrs, format
the cell as [hh]:mm.

Confused about this, though:

If you have FIVE 8hr entries, wouldn't that be:

1 day 16 hours ?

Biff
Click to expand...

oops... Stupid typo on my part. Yes it should read as 16hrs.

Your suggestion got me closer though. It just bunded it up as 40hrs. By
changing that to dd:hh:mm it seems to work just fine.

now how about this one....

is it possible to have a field interepret a time written as "2:05" as a flat
number 125 (the number of minutes)?

I ask because I'm also tracking fuel burned per hour.

I have the following columns defined:

I= Starting time (format is: hh:mm)
J= Ending time (format is: hh:mm)
K= Actual flight time (format is: hh:mm)
Formula is: =IF(AND(ISBLANK(I6),ISBLANK(J6)),"",J6-I6)
L= Fuel at take off (entered as pounds)
M= Fuel at landing (entered as pounds)
N= Actual fuel burned
Formula is: =IF(AND(ISBLANK(L6),ISBLANK(M6)),"",L6-M6)
O= Fuel burned per hour (this is my sticking point)

I'd like it to take N and divide it by K but it needs to understand the time as
minutes only. I can't seem to figure this one out. What I'm doing for now is
just using another field (R actually) to manually input the number of minutes
and using the formula: =SUM(N6/(R6/60))

Any other thoughts?

Thankz again.


Anchor's aweigh,

The Admiral
(e-mail address removed)
 
I've got a
sheet set up to track times spent flying.
Click to expand...

Me too and I was given a lot of help previously in ths newsgroup so that
I now have an electronic replica of the official flying log book. I also
have a section whereby you can find out hours in a particular plane,
between particular dates - in fact anything you want to put in. If you
wish I'll send it to you via PM.
 
Hi!

=N/(K*1440)

Suggestions:

Change K to:

=IF(OR(I6="",J6=""),"",J6-I6)

Change N to:

=IF(OR(L6="",M6=""),"",L6-M6)

Biff
-----Original Message-----
Hi!

To stop your total from rolling back after 24hrs, format
the cell as [hh]:mm.

Confused about this, though:

If you have FIVE 8hr entries, wouldn't that be:

1 day 16 hours ?

Biff
Click to expand...

oops... Stupid typo on my part. Yes it should read as 16hrs.

Your suggestion got me closer though. It just bunded it up as 40hrs. By
changing that to dd:hh:mm it seems to work just fine.

now how about this one....

is it possible to have a field interepret a time written as "2:05" as a flat
number 125 (the number of minutes)?

I ask because I'm also tracking fuel burned per hour.

I have the following columns defined:

I= Starting time (format is: hh:mm)
J= Ending time (format is: hh:mm)
K= Actual flight time (format is: hh:mm)
Formula is: =IF(AND(ISBLANK(I6),ISBLANK(J6)),"",J6- I6)
L= Fuel at take off (entered as pounds)
M= Fuel at landing (entered as pounds)
N= Actual fuel burned
Formula is: =IF(AND(ISBLANK(L6),ISBLANK(M6)),"",L6- M6)
O= Fuel burned per hour (this is my sticking point)

I'd like it to take N and divide it by K but it needs to understand the time as
minutes only. I can't seem to figure this one out. What I'm doing for now is
just using another field (R actually) to manually input the number of minutes
and using the formula: =SUM(N6/(R6/60))

Any other thoughts?

Thankz again.


Anchor's aweigh,

The Admiral
(e-mail address removed)
.
Click to expand...
Click to expand...
 
I've got a sheet set up to track times spent flying.
Click to expand...

Sorry to send this publicly but I did try to send the xls file to your
mail address but it has timed out for some reason. I'm not ignoring you
and will try it again.
 
Sorry to send this publicly but I did try to send the xls file to your
mail address but it has timed out for some reason. I'm not ignoring you
and will try it again.
Click to expand...


Not a prob. I presume that you're referring to the return address of the email
I sent you. The address that the newsgroups shows is not valid.

Perhaps one of our servers is puking on the .xls extension as a safety measure.
Maybe zipping it first might help? Just a thought.

I appreciate your taking the time to do this.



Anchor's aweigh,

The Admiral
(e-mail address removed)
 
Perhaps one of our servers is puking on the .xls extension as a safety measure.
Maybe zipping it first might help? Just a thought.
Click to expand...

Good point. I'll try again.
 
Perhaps one of our servers is puking on the .xls extension as a safety measure.
Maybe zipping it first might help? Just a thought.
Click to expand...

Big problems somewhere :-(

Tried zipping and then just a plain message to you with no attachment at
all. Even tried after turning Kerio firewall off just in case it was
that. Don't know what to do as your mails reach me fine.
 
Big problems somewhere :-(

Tried zipping and then just a plain message to you with no attachment at
all. Even tried after turning Kerio firewall off just in case it was
that. Don't know what to do as your mails reach me fine.
Click to expand...


Will send you a different email to try.....



Anchor's aweigh,

The Admiral
(e-mail address removed)
 
Back
Top