Another Excel Question - this time about "hours"

[Spotting Bad Guys]

Hi all,

Looking for a bit of help with a spreadsheet I've made up to log flight hours. Basically, I want to insert takeoff time i.e. 17:45, then insert land time i.e. 00:30 and have the programme calculate the flight duration to the minute.

I've got it working so that as long as both events take place the same day, everything is fine. The trouble comes when the aircraft lands the next day and you have 00:30 - 17:45 and no total!

I'm using Excel97 SR-1 (I know, it's the company spec). Any thoughts?

Cheers

SBG

[MetoPower]

Try this:

Assuming A1 is OFF CHKS, and B1 CHKS ON, then:
in C1: =IF(B1<A1,B1+1,B1)-A1
Of course do not forget to format hh:mm

MP

[bnt]

Excel does do full time calculations, but you'd need to enter the full date and time in a standard format. Then it'll recognised as such by Excel, and it'll know that the 00:30 is from the next day.

[Keef]

Or, put an extra bit in the time calculation that if arrival time is less than departure time, add 24 hours.

Departure and arrival times and elapsed time all in hh:mm. Dep in col A, Arr in col B
Flight time is computed by =IF(B1>A1,B1-A1,B1-A1+24)

The catch is, of course, that if you put in a silly time you'll get a silly result.

[spannersatcx]

input 00:30 as 24:30 and it will calculate it.

[Spotting Bad Guys]

Thanks all for the replies - very helpful and it's sorted now. I had tried the "24:30" trick but it was wise to that one and kep resetting it to 00:30!
The answer provided by metopower did the trick - thanks also BNT but as the date is already shown in a column header I was reluctant to input it a second time.
Cheers!
SBG

[stickyb]

Another useful trick to remember is that if you format a cell as hh:mm:ss or something similar, then that is what is displayed, even if the cell contains a full date and time. (In fact all date/time cells contain a full date and time, starting from 1900/01/01)

So you can hold full date/time in cells but only display the time portion.

[Spotting Bad Guys]

Aaaahhh. So can you also get it to use the system clock to generate the date?

[cdtaylor_nats]

Yes you can use

TODAY() for todays date or
NOW() for the current date & time

[Keef]

Those functions are brilliant for doing the "last 30 days" etc stuff.

BUT they update whenever you open the sheet, so don't use them for individual log entries.

[Spotting Bad Guys]

right. I was trying that last night.

What I want to do is have the column header as the date, then below have t/o, land, on- and off- station times, plus some additional data below - serviceability etc.

It's working so far.....but is still a bit mandraulic....

SBG