I am trying to calculate Flight Time - but the time enroute may or may not
cross over midnight.

IE:

Depart 23:10 Arrive 01:55 (looking for the result of 2.75 hours)
Depart 02:20 Arrive 02:45 (looking for the result of .42 hours)

Is there one formula I can use for all of column C to calculate time
regardless if it flies past midnight?

thanks

Try this:

=ROUND(MOD(arrive-depart,1)*24,2)

A1 = depart = 23:10
B1 = arrive = 1:55

=ROUND(MOD(B1-A1,1)*24,2)

Format as General or Number

--
Biff
Microsoft Excel MVP


"JB Bates" wrote in message
...
I am trying to calculate Flight Time - but the time enroute may or may not
cross over midnight.

IE:

Depart 23:10 Arrive 01:55 (looking for the result of 2.75 hours)
Depart 02:20 Arrive 02:45 (looking for the result of .42 hours)

Is there one formula I can use for all of column C to calculate time
regardless if it flies past midnight?

thanks

Hi,

Times in a1 (Depart) and b1 (Arrival). Drag down as required

=(B1-A1+(B1<A1))*24

Mike

"JB Bates" wrote:

I am trying to calculate Flight Time - but the time enroute may or may not
cross over midnight.

IE:

Depart 23:10 Arrive 01:55 (looking for the result of 2.75 hours)
Depart 02:20 Arrive 02:45 (looking for the result of .42 hours)

Is there one formula I can use for all of column C to calculate time
regardless if it flies past midnight?

thanks

Awesome, awesome, awesome! THANK YOU for the speedy response! JB

"T. Valko" wrote:

Try this:

=ROUND(MOD(arrive-depart,1)*24,2)

A1 = depart = 23:10
B1 = arrive = 1:55

=ROUND(MOD(B1-A1,1)*24,2)

Format as General or Number

--
Biff
Microsoft Excel MVP


"JB Bates" wrote in message
...
I am trying to calculate Flight Time - but the time enroute may or may not
cross over midnight.

IE:

Depart 23:10 Arrive 01:55 (looking for the result of 2.75 hours)
Depart 02:20 Arrive 02:45 (looking for the result of .42 hours)

Is there one formula I can use for all of column C to calculate time
regardless if it flies past midnight?

thanks

If I also wanted to apply this to duty time, but where they may not be on and
off duty the same time can you advise how i would do that?

Duty On Duty Off
Leg 1 1300
Leg 2
Leg 3 2359

how can I have it look at the duty off column and keep looking till it finds
a time?

thanks

"T. Valko" wrote:

Try this:

=ROUND(MOD(arrive-depart,1)*24,2)

A1 = depart = 23:10
B1 = arrive = 1:55

=ROUND(MOD(B1-A1,1)*24,2)

Format as General or Number

--
Biff
Microsoft Excel MVP


"JB Bates" wrote in message
...
I am trying to calculate Flight Time - but the time enroute may or may not
cross over midnight.

IE:

Depart 23:10 Arrive 01:55 (looking for the result of 2.75 hours)
Depart 02:20 Arrive 02:45 (looking for the result of .42 hours)

Is there one formula I can use for all of column C to calculate time
regardless if it flies past midnight?

thanks

If there's only one entry in each of On-Off but the cell address isn't
constant:

=MOD(MAX(C1:C5)-MAX(B1:B5),1)

Format as h:mm or [h]:mm

--
Biff
Microsoft Excel MVP


"JB Bates" wrote in message
...
If I also wanted to apply this to duty time, but where they may not be on
and
off duty the same time can you advise how i would do that?

Duty On Duty Off
Leg 1 1300
Leg 2
Leg 3 2359

how can I have it look at the duty off column and keep looking till it
finds
a time?

thanks

"T. Valko" wrote:

Try this:

=ROUND(MOD(arrive-depart,1)*24,2)

A1 = depart = 23:10
B1 = arrive = 1:55

=ROUND(MOD(B1-A1,1)*24,2)

Format as General or Number

--
Biff
Microsoft Excel MVP


"JB Bates" wrote in message
...
I am trying to calculate Flight Time - but the time enroute may or may
not
cross over midnight.

IE:

Depart 23:10 Arrive 01:55 (looking for the result of 2.75 hours)
Depart 02:20 Arrive 02:45 (looking for the result of .42 hours)

Is there one formula I can use for all of column C to calculate time
regardless if it flies past midnight?

thanks

When I use this formula, it works for anything PAST midnight but I don't get
a correct calculation if the time doesn't go past midnight. Will it not work
both ways?

"JB Bates" wrote:

I am trying to calculate Flight Time - but the time enroute may or may not
cross over midnight.

IE:

Depart 23:10 Arrive 01:55 (looking for the result of 2.75 hours)
Depart 02:20 Arrive 02:45 (looking for the result of .42 hours)

Is there one formula I can use for all of column C to calculate time
regardless if it flies past midnight?

thanks

You haven't told us which formula, nor what values you have when you "don't
get a correct calculation".

Try =MOD(B1-A1,1)*24 and format as general or number, not as time.
--
David Biddulph

mailrail wrote:
When I use this formula, it works for anything PAST midnight but I
don't get a correct calculation if the time doesn't go past midnight.
Will it not work both ways?

"JB Bates" wrote:

I am trying to calculate Flight Time - but the time enroute may or
may not cross over midnight.

IE:

Depart 23:10 Arrive 01:55 (looking for the result of 2.75 hours)
Depart 02:20 Arrive 02:45 (looking for the result of .42 hours)

Is there one formula I can use for all of column C to calculate time
regardless if it flies past midnight?

thanks