Ceiling with some added bits!

[Gwill1983]

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!

 

[AlanY]

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.

 

[Gwill1983]

Apologies.

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

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.