Manpower formula

Sidonia Posteuca

Hello,

I do not know if this is possible, however it would make life so much easier if it were.

Can I set up a formula in one column to calculate how many people (man power) I need on a specific job based on the start and end date (when the job needs to be completed), the budget hours (hours given to complete the job) and the fact that they will be working an 8 hour work day.

This way, when I am planning a job, I can simply input my start and end date for the project, cross checked with the hours I have been budgeted to give me the number of crew members I need on the job each day.

If this is possible, what is the formula and how do I make it work?

Jeff Hoelzel

Can you provide all your conditions and variables? Maybe provide an example like, a job that takes 5 days (40 hours) to complete, assuming an 8 hour workday, requires a crew of 4 (manpower = 1x10)?

David Joyeuse

Hi @Sidonia Posteuca

=ROUNDUP([Budget Hours]@row/(([End Date]@row - [Start Date]@row) * 8)) Should do it.

Sidonia Posteuca

Perfect. Thank you!

Sidonia Posteuca

Follow up question:

If I wanted to calculate how many crew members I will need in the next week, month, 6 months, etc. based on the Start Date and Finish Date columns. What would be the formula for that?

Sean Morgan

Hello @Sidonia Posteuca ,

Although this formula isn't exactly what you are looking for, as it counts the amount of date entries within a range, the way the range is laid out is how I would calculate something, based on a date range.

"=COUNTIFS([Expiration Date]:[Expiration Date], >=DATE(2020, 5, 1), [Expiration Date]:[Expiration Date], <=DATE(2020, 10, 1))"

Contrary to this, if you are able to provide a Screenshot of your Sheet, whilst removing sensitive data, I can help create something more bespoke for your scenario

Regards

Sean