Calculating Time

[Paul Cooke]

Time is a wonderful thing......only I have spent ages trying to sor
this problem out with no success, so would be really grateful for som
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.7
Hours.

The problem I am having is working out a formula to round the tota
minutes worked up to the next half hour eg 7.51 or above rounded up t
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 wor
on??

Many thanks for any help anyone can offer me

 

[Guest]

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

 

[Bob Phillips]

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

--

HTH

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

 

[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)