Rounding of Time

RagDyer · 09-06-2005, 03:05 AM

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" <[email protected]> wrote in message
news:[email protected]...
> 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

RagDyer · 09-06-2005, 10:05 AM

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" <[email protected]> wrote in message
news:[email protected]...
> 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

RagDyer · 09-06-2005, 12:05 PM

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" <[email protected]> wrote in message
news:[email protected]...
> 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

RagDyer · 09-06-2005, 03:05 PM

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" <[email protected]> wrote in message
news:[email protected]...
> 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

RagDyer · 09-06-2005, 06:05 PM

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" <[email protected]> wrote in message
news:[email protected]...
> 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

Morten · 09-06-2005, 07:05 PM

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

RagDyer · 09-06-2005, 07:05 PM

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" <[email protected]> wrote in message
news:[email protected]...
> 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