i need hourly breakdown based off start and finish dates

abowles

hello! I am attempting to find a breakdown of hours needed per line item, relative to start and finish dates. example: if something takes 2 days and 10 hours and is planned to start on the last day of january and end on the first day of february, i need it to say 5 hours in january, and 5 hours in february. (average of hours over duration then summed up to determine number of hours needed by month of labor.

Julie Fortney

How do you want this to be displayed? Could you provide a mockup of where you'd want that information to appear?

abowles

ideally i'd have an output sheet where i can pull multiple sources together. honestly not picky on where it is, as long as i can get the info together. my biggest struggle right now is having a formula that knows how to break down a start to finish to say "X amount of days in one month and X amount of days in another"

Julie Fortney

something like this?

Formulas:

Number of Months: =(YEAR(Finish@row) - YEAR(Start@row)) * 12 + MONTH(Finish@row) - MONTH(Start@row) + 1

Hours per Month: =[Est Hours]@row / [Number of Months]@row

abowles

this is VERY close to what i'm looking for! unfortunately, while there is an average going on with hours/month, i still need a list of months, aka columns saying "january 2024, february 2024" and so on. with the hours per month outputting there.

Kind of long, but this is what I came up with, what do you think? this way i can column each month, and get a sum total at the bottom of the sheet. do you see this breaking?

IF(OR(MAX(Start@row, DATE(2024, 4, 1)) > DATE(2024, 4, 30), MIN(Finish@row, DATE(2024, 4, 30)) < DATE(2024, 4, 1)), 0, NETWORKDAYS(MAX(Start@row, DATE(2024, 4, 1)), MIN(Finish@row, DATE(2024, 4, 30))))

abowles

the example above is just for the month of April, I would have to change the range in each column

Julie Fortney

This help article has several great ideas for displaying the months between two dates:

https://community.smartsheet.com/discussion/107311/display-the-text-of-active-months-based-on-a-date-range