I've set up a forecast model to forecast billings and estimated costs so I can update it every month and it will calculate based on the remaining contract amounts to bill and the remaining estimated costs when I input updated start dates and end dates. We get updated schedules all the time and most of dates for our tasks for each job change so much, that it's too time consuming to try to manually calculate.

It's set up to calculate based on weeks for the duration, but I run into the dates falling into partial weeks and it either over calculates a week or under calculates a week.

I've thought of using days as the duration, but more often than not we work Saturdays, sometimes on holidays, and even if we're not working on holidays, our Operators and foreman get paid holidays.

Is there a formula or a different way to calculate the duration that will compensate for the partial weeks?



Thank you so much for any suggestions. I've uploaded a sample of the worksheet I'm working on.

Attached Files

Budget Forescast Model.xlsm (234.3 KB, 15 views)