Ceiling with some added bits!

Gwill1983

Board Regular
Joined
Oct 24, 2018
Messages
124
Office Version
  1. 365
Platform
  1. Windows
Hi all,

I am trying to create a calculation in my spreadsheet which takes various time entries and calculates how many half days I need to charge my customer.

So... currently I have:

Hours required on site in cell D32
Inbound travel time in cell D35
Outbound travel time in cell D36
Site Availability in E11

The labour times are all SUM'd in cell D39 to give a total labour time and then in cell D42 I am using the formula below to round the labour requirement to the nearest half day (based on the site availability hours) for pricing

=CEILING(D39/E11,0.5)

This is working perfectly, but I now need to adjust this slighty and I am struggling to work out how to do it.

I need to take the hours required on site (D32) against the site availability hours (E11) to equate how many site days are required. I then need to include the calculation of the travel time to establish whether the addition of these hours need to increase the 'days' required to charge or if the total time will still fall within the days required for site labour.

I dont feel like I have explained this very well, but I am becoming very bamboozled so any help would be greatly received!
 

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN
Hi all,

I am trying to create a calculation in my spreadsheet which takes various time entries and calculates how many half days I need to charge my customer.

So... currently I have:

Hours required on site in cell D32
Inbound travel time in cell D35
Outbound travel time in cell D36
Site Availability in E11

The labour times are all SUM'd in cell D39 to give a total labour time and then in cell D42 I am using the formula below to round the labour requirement to the nearest half day (based on the site availability hours) for pricing

=CEILING(D39/E11,0.5)

This is working perfectly, but I now need to adjust this slighty and I am struggling to work out how to do it.

I need to take the hours required on site (D32) against the site availability hours (E11) to equate how many site days are required. I then need to include the calculation of the travel time to establish whether the addition of these hours need to increase the 'days' required to charge or if the total time will still fall within the days required for site labour.

I dont feel like I have explained this very well, but I am becoming very bamboozled so any help would be greatly received!
it will be easier for helpers if you can provide sample data and expected output.
 
Upvote 0
Apologies.

I have the sheet below (ignore the formatting and borders being a shambles. Will fix them once it works!)

1676464100160.png


The problematic cell is D42 which is the outcome of the rounded labour in working days. Currently, this contains the formula =CEILING(D39/E11,0.5)
The value in D39 is the total labour (inc travel) required. The formula takes into account the workable site hours in E11, compares them to the hours in D39 and then tells me how many days are required (rounded to the nearest 0.5 day).

What I want it to provide is the days required (rounded to the closest half day) by comparing the no. of hours on site against the workable hours and then adding the travel time as the travel can happen outside of the accessible hours.

For instance, if:

Access to site = 5 hours
site labour time = 4 hours
travel time = 1 hour each way

This would currently request us to charge 1.5 days as the total hours required (6) are more than the accessible site hours (5). This would actually only need to be 1 day because the site labour fits within the accessible hours, and then the site labour + travel time is less than 8 hours.



I am hoping this makes sense.
 

Attachments

  • 1676464077695.png
    1676464077695.png
    37.4 KB · Views: 7
Upvote 0

Forum statistics

Threads
1,223,236
Messages
6,170,912
Members
452,366
Latest member
TePunaBloke

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top