Help with MOD function

[perplex]

Hi all

I'm trying to create a sheet that acts as a roster, adding morin
shifts and afternoon shitfs, calculating the answer, then depending o
the times round to x.5 or down to x.0


ie: 8.15 to 12.30 12.15 to 16.45


While i can get Excel to work out the hours worked, I cant get i
automatically round up or down as required.

A work colleague suggested =IF(MOD(A4,1) <>0,INT(A4)+0.5,INT(A4))

but in the above scenario, total hours worked is 8.1 - but the sheet i
telling me 8.5 (as its adding the 0.5 if the MOD is <>0)

Can someone please help!! or suggest a more simple(?) solution?!

Thanks
perple

 

[Ron Rosenfeld]

Hi all

I'm trying to create a sheet that acts as a roster, adding moring
shifts and afternoon shitfs, calculating the answer, then depending on
the times round to x.5 or down to x.0


ie: 8.15 to 12.30 12.15 to 16.45


While i can get Excel to work out the hours worked, I cant get it
automatically round up or down as required.

A work colleague suggested =IF(MOD(A4,1) <>0,INT(A4)+0.5,INT(A4))

but in the above scenario, total hours worked is 8.1 - but the sheet is
telling me 8.5 (as its adding the 0.5 if the MOD is <>0)

Can someone please help!! or suggest a more simple(?) solution?!

Thanks
perplex

Well, I am perplexed as to how you obtain 8.1 hours worked from the above.

If your decimal digits represent start and stop times for each shift, then the
first one looks like 4 hrs 15 minutes; and the second shift looks like 4 hrs 30
minutes. The total would be 8 hrs 45 minutes which I would think you would
want to round to 9 hrs.

Probably you should be entering the values as 'times'. If your regional
settings are set to use the decimal as the time separator, then the above is
OK. Otherwise enter the data, for example, as 8:15 to 12:30.

To round a "time" to the nearest 1/2 hour, use this formula:

=ROUND(A4/TIME(0,30,0),0)*TIME(0,30,0)

and format the result as [h]:mm.

If you want the result as a decimal number, then multiply by 24.


--ron