Rounding of Time
 

Hey all!

I'm trying to make a timesheet to log my flight-hours.
I've prutty much managed to make it like I want to, but a few function is
missing.

Ex 1)
Takoff-time 12:00 Landing-time 12:59 = Total flight-time 00:59
How do I manage to get Total Flight-time rounded to the nearest 5min? It's
nopt a problem with numbers, but time i can't find out of.

Ex 2)
I want to make the sheet sum every 18th cell and post it at another spot
saying:
Page1 50h
Page2 36h
Page3 19h
etc.

Is there a formula that can do this for me. or do i have to Type
Page1,Page2..... and then in then next cell sum every 18th cell manualy?

Hope someone can help me

Yours
Morten

There are probably neater ways of doing this, but this does work

You will need to create a table with each time increment from midnight
to 23:55 from A6 expressed as time 00:00, 00:05, 00:10 etc. In the
next column (from B6)these need to be expressed as decimal numbers with
at least 4 numbers after the decimal: 0.000, 0.0035, 0.0069, 0.0104 etc.
You can do this by starting with one cell in B3 with 5 minutes as a
decimal in it (0.003472) and just mulitply it up. These two columns are
going to become your look up table.
Next thing is to set up your Take off and Landing times either one
under the other or side by side (in this example they are one under the
other) Enter these as times: 12:00 and 12:59. Set the next column to
read these two numbers ( =E3) if the time is in D3, but format it in
decimal not time. Perform the calculation of subtracting the landing
time from the takeoff time in column E using the time notation. You
will end up with 0:59. Under the the take off and landing times in
column D you will need to set a lookup formula to look at the answer in
ColE and compare it with the lookup table. The formula you'll need is
=vlookup(e8,a6:b293,2)+b3. This will find the nearest decimal under
0:59 which is 0.0382 and add another 5 minutes to it


--
Jon Quixley
------------------------------------------------------------------------
Jon Quixley's Profile: http://www.excelforum.com/member.php...o&userid=25803
View this thread: http://www.excelforum.com/showthread...hreadid=393277

You say "Flight Hours" but your example is in minutes.
Would you be satisfied with just minutes?
Or would you prefer hours and minutes?

This is a formula that's usually used for payroll:

=((B1-A1+(B1<A1))*24)*AND(B1<0,A1<0)

The formula is in a cell formatted as General or Number,
Where start time is in A1, end time is in B1,
And it will account for times *across midnight*.
It's set to return decimal hours.

You can revise it to return minutes:

=((B1-A1+(B1<A1))*24*60)*AND(B1<0,A1<0)

And then round to the nearest 5 minutes:

=ROUND((((B1-A1+(B1<A1))*24*60)*AND(B1<0,A1<0))/5,0)*5

NOW, with this formula in C1, giving you total rounded minutes, you could
add these other two formulas to break it down to hours and minutes:

For hours in D1:
=INT(C1/60)

For minutes in E1:
=MOD(C1,60)

--
HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================

"Morten" wrote in message
...
Hey all!

I'm trying to make a timesheet to log my flight-hours.
I've prutty much managed to make it like I want to, but a few function is
missing.

Ex 1)
Takoff-time 12:00 Landing-time 12:59 = Total flight-time 00:59
How do I manage to get Total Flight-time rounded to the nearest 5min? It's
nopt a problem with numbers, but time i can't find out of.

Ex 2)
I want to make the sheet sum every 18th cell and post it at another spot
saying:
Page1 50h
Page2 36h
Page3 19h
etc.

Is there a formula that can do this for me. or do i have to Type
Page1,Page2..... and then in then next cell sum every 18th cell manualy?

Hope someone can help me

Yours
Morten