Formulas and Functions

Formulas and Functions

Ask for help with your formula and find examples of how others use formulas and functions to solve a similar problem.

i need hourly breakdown based off start and finish dates

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.

Answers

  • Overachievers

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

  • 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"

  • Overachievers

    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

  • 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))))

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

  • Overachievers

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

    Display the text of Active Months based on a date range

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!

Trending in Formulas and Functions