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

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

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

--

HTH

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


"Paul Cooke" wrote
in message ...

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

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

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" wrote
in message ...

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