# Ceiling(MOD...

Status
Not open for further replies.

#### Secty516

##### New Member
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!

### Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.
=roundup(mod((i5-"00:15")-h5,1)*48,0)/2

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

Status
Not open for further replies.

Replies
34
Views
982
Replies
5
Views
400
Replies
6
Views
2K
Replies
1
Views
391
Replies
3
Views
379

1,203,455
Messages
6,055,540
Members
444,794
Latest member
HSAL

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back