Ceiling(MOD...

[Secty516]

I currently have a timesheet w/24hr shifts. I use the flwg formula:

=CEILING(MOD(I5-H5,1),"00:30:00")*24

However I would like to know if there is a way of being even more specific. i.e. if they work until 16min after the hour, it should round to 30min and at 46min after the hour should round to the next hour.

for example 10:00 - 18:15 = 8hrs, 10:00 - 18:16 = 8.5hrs, 10:00 - 18:45 = 8.5hrs, 10:00 - 18:46 = 9hrs

any suggestions?

Just trying to make my life easier, but cannot for the life of me figure this out...

Any help would be appreciated.

Thank you!

 

[Scott Huish]

=roundup(mod((i5-"00:15")-h5,1)*48,0)/2

 

[schielrn]

I currently have a timesheet w/24hr shifts. I use the flwg formula:

=CEILING(MOD(I5-H5,1),"00:30:00")*24

However I would like to know if there is a way of being even more specific. i.e. if they work until 16min after the hour, it should round to 30min and at 46min after the hour should round to the next hour.

for example 10:00 - 18:15 = 8hrs, 10:00 - 18:16 = 8.5hrs, 10:00 - 18:45 = 8.5hrs, 10:00 - 18:46 = 9hrs

any suggestions?

Just trying to make my life easier, but cannot for the life of me figure this out...

Any help would be appreciated.

Thank you!

It is not advised to create duplicate posts on the same question:

http://www.mrexcel.com/forum/excel-questions/696470-timesheet-computations.html