Calculating Time

[Paul Cooke]

Time is a wonderful thing......only I have spent ages trying to sort this problem out with no success, so would be really grateful for some help from the experts please ...

I have a sheet which is designed as a "work timesheet",

Column D is a start time Eg 09:00
Column E is the finish time EG 16:45

Column G is is a formula to calculate the number of hours worked " =(E1-D1)*24 " which from the above example gives a return of 7.75 Hours.

The problem I am having is working out a formula to round the total minutes worked up to the next half hour eg 7.51 or above rounded up to 8.00, or if the total was 7.49 or below for it to be rounded up to 7.5

The actual hours do vary so I guess its just the minutes I need to work on??

Many thanks for any help anyone can offer me.

[Search33]

G1 is =CEILING((E1-D1)*24,0.5)

"Paul Cooke" wrote:

>
> Time is a wonderful thing......only I have spent ages trying to sort
> this problem out with no success, so would be really grateful for some
> help from the experts please ...
>
> I have a sheet which is designed as a "work timesheet",
>
> Column D is a start time Eg 09:00
> Column E is the finish time EG 16:45
>
> Column G is is a formula to calculate the number of hours worked "
> =(E1-D1)*24 " which from the above example gives a return of 7.75
> Hours.
>
> The problem I am having is working out a formula to round the total
> minutes worked up to the next half hour eg 7.51 or above rounded up to
> 8.00, or if the total was 7.49 or below for it to be rounded up to 7.5
>
> The actual hours do vary so I guess its just the minutes I need to work
> on??
>
> Many thanks for any help anyone can offer me.
>
>
> --
> Paul Cooke
> ------------------------------------------------------------------------
> Paul Cooke's Profile: http://www.excelforum.com/member.php...o&userid=29268
> View this thread: http://www.excelforum.com/showthread...hreadid=489876
>
>

[Bob Phillips]

=ROUNDUP((E1-D1)*48,0)/2

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Paul Cooke" <[email protected]> wrote
in message news:[email protected]...
>
> Time is a wonderful thing......only I have spent ages trying to sort
> this problem out with no success, so would be really grateful for some
> help from the experts please ...
>
> I have a sheet which is designed as a "work timesheet",
>
> Column D is a start time Eg 09:00
> Column E is the finish time EG 16:45
>
> Column G is is a formula to calculate the number of hours worked "
> =(E1-D1)*24 " which from the above example gives a return of 7.75
> Hours.
>
> The problem I am having is working out a formula to round the total
> minutes worked up to the next half hour eg 7.51 or above rounded up to
> 8.00, or if the total was 7.49 or below for it to be rounded up to 7.5
>
> The actual hours do vary so I guess its just the minutes I need to work
> on??
>
> Many thanks for any help anyone can offer me.
>
>
> --
> Paul Cooke
> ------------------------------------------------------------------------
> Paul Cooke's Profile:
http://www.excelforum.com/member.php...o&userid=29268
> View this thread: http://www.excelforum.com/showthread...hreadid=489876
>

[Paul Cooke]

Hi Bob,

Many thanks for your prompt reply, it works perfectly, only if you have the time could you explain how you did it so i can undersatand for the future

Many thanks again

Regards

Paul

[Bob Phillips]

The first thing to do is to get into units of half an hour. As you know,
multiplying by 24 gets it into units of 1 hours, so multiplying by 48 gets
it in units of half an hour (such as 7.326 half hours).

Rounding this up to 0 decimal places gets us an integer number of half hour
units.

Dividing by 2 gets it back into hour units.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Paul Cooke" <[email protected]> wrote
in message news:[email protected]...
>
> Hi Bob,
>
> Many thanks for your prompt reply, it works perfectly, only if you have
> the time could you explain how you did it so i can undersatand for the
> future
>
> Many thanks again
>
> Regards
>
> Paul
>
>
> --
> Paul Cooke
> ------------------------------------------------------------------------
> Paul Cooke's Profile:
http://www.excelforum.com/member.php...o&userid=29268
> View this thread: http://www.excelforum.com/showthread...hreadid=489876
>