# I'd like to see how much work is scheduled per month based on total price and start and end dates.

• 0
• Question
• Updated 3 years ago

I've got a report that shows the duration of projects on a timeline. Our projects can last from 3 days to 5 months. I'd also like to see if we've scheduled enough/too much work for each month, so I want to have a report that shows how much we've scheduled each month based on the total of portions of various projects. I was thinking maybe I could take the project total/duration to get a daily total, but I can't figure out how to make a report to total a certain month's scheduled work total. I'm thinking I might have to create a new child table, but I'm open to any suggestions.

• 0 Points

Posted 3 years ago

• 0
• 72,226 Points
I can tell you the approach, but I don't have the time to work out the exact formulas. You can contact me off line via the information in my profile if you need the formulas worked out.

I suggest that you only likely care about say the next 6 months plus the balance of the current month.

Building on your thought about using the daily work content in each project I would make a formula to calculate Project Hours Current month plus 1. i.e. Next Month.

You would set up formula variables for the first of the Current month plus 1, and the last day of the current month plus 1.

Then calculate how many days that project was active for between those two dates, multiplied by the daily average work content.

Once you get that formula working, it's a trivial matter to copy it and adjust it to give Project Hours Current month plus 2 i.e., month after Next.

Repeat for about 6 months out and if you like the current month remaining and then you just make a simple table report with a list of projects with hours in any if those 7 fields, and have the project name and those 7 fields as columns.

As an aside, you probably want to calculate workdays excluding weekends to get your daily work content.
• 0 Points
Super awesome advice! I got that to work and it's coming out great. Thank you very much! Now I'm trying to get the field label to show up as a month depending on the current date. Right now I've got the field labeled "Month \$ -1" for last month's portion and "Month \$ 0" for this month and "Month \$ 1" for next month and so on. But I'd really like to make them say "December", "January", and "February", but change relative to the current date. Is it possible to use formulas in the field label?
• 0 Points
And just for the record, here's the formula I came up with, but I'm sure yours would have been much more elegant.
[Estimated Total Price]/WeekdaySub([Calculated Fab at 90% Date],[Calculated Fab Start Date])*
WeekdaySub([Calculated Fab at 90% Date],[Calculated Fab Start Date]),