Projected DATE/TIME between specific hours

[additude]

I have a worksheet that calculates how many days and hours a certain inventory item amount will last, based on a consumption rate of that item and a date the item was last inventoried.

My result is currently displayed as a DATE/TIME value as to when the expected inventory item will be exhausted.

Currently my resulting DATE/TIME value is based on the number of hours of operation of the business where the inventory item is used, (ie. a consumption rate based on 13.5 hours of operation per day between the hours of 9 AM and 10:30 PM). However the hours of operation can and will change.

I'm having difficulty creating a formula that would make my DATE/TIME value showing when the item will become exhausted and be displayed within the hours of operation of the business.

Some inputs will calculate a DATE/TIME that the inventory item will become exhausted outside the business hours of operation, which is incorrect because the item is not in use outside the hours of operation.

I'm not really sure where to go with creating my formula to work within this criteria.

Thanks for any help and suggestions.

I've attached a sample worksheet.

[daddylonglegs]

I take it you want to include all days of the week. Try this formula in E14

=CEILING(((MOD(E10,1)-E7)*24+E9/E12)/E8,1)-1+E10+(E9/E12-CEILING((MOD(E10,1)-E7)*24+E9/E12,E8)+E8)/24

[additude]

**Shweeet!!

Yes, open 7 days a week and the formula worked really nice.

Thanks a group!