Creating a utilization report - Formula needed

Greetings, I'm looking to create a "weekly report" of machine usage.

each machine has its own row, followed by its current hours. the hours are fed in automatically through API feed & overwrite daily. (if the machine reports hours in the date will update automatically also)

I think to create a basic utilization I need to reference a date range to pull the data into a column that represents a previous day. (such as "if Date -1 =Current hours"), followed by a string of 6 additional columns to "store" a weeks worth of data, then from there I can create a basic =sum formula to get my weekly utilization.

Any thoughts on what type of formula to grab the current hours for today-1, today-2, today-3, etc...

note* this is machine operation time (such as a operator using a machine 8 hours a day) but the number is always different - can be zero all the way to 24 hour operation per day

sample chart below for reference


thank you!


Answers

  • Genevieve P.
    Genevieve P. Employee Admin

    Hi @MichaelB

    The way I would do this is to use the WEEKNUMBER Function. Try adding a helper column with a Column Formula of:

    =WEEKNUMBER([Date hours reported]@row)

    Then once each row has a Week Number associated, the easiest thing to do would be to use a Report to Group by Week Number and then use the Summarize function to SUM the Current Hours. See: Redesigned Reports with Grouping and Summary Functions

    Let me know if this would work for you!

    Cheers,

    Genevieve

  • Thank you for the idea! I have not used that function before & it seems to work great.

    that would solve a portion of it but my main issue would revolve around counting each row items individual hours for the week (then later I can sum it for the whole fleet)

    Example: Each machine runs Daily & counts its total hours up. (like the odometer on a car - but in hours)

    Machine A runs a 8 hour shift every day but none on the weekend.

    April 3 Sun: 1048 hours

    April 4 Mon: 1056 hours

    April 5 Tue: 1064 hours

    April 6 Wed: 1072hors

    April 7 Thu: 1080 hours

    April 8 Fri 1088 hours

    April 9 Sat: 1088hours

    April 10 Sun: 1088 hours


    I need to capture how many hours were ran by Machine for the week. (Machine A ran 40 hours over the week) so my "total" would be 40, not the 1088 reading.

    unsure how to take a automatically overwriting cell (Hours & Date) and "store" the results (such as its cell history) for the past 7 days. i'm going to test out dumping all new values on a separate sheet & doing a index match / count if combination to reference it. that may get me a (count if under 7 days old) feature - but I'll need to constantly dump out old data to stay within sheet limits.

  • Genevieve P.
    Genevieve P. Employee Admin

    Hi @MichaelB

    If your data is coming in cumulatively, I would suggest setting up a formula that simply subtracts one Sunday value from another, so you can see 40 instead of 1088.

    In your Second sheet, you could drag fill down a whole Date column to create a column of all Sunday dates. Then yes, you could use an INDEX(MATCH to bring in the matching Hour value based on the Sunday date listed, and the 7 days before the Sunday date listed.

    First you can find the match for the Sunday Date in the cell to the left:

    =INDEX({Hours Column}, MATCH([Sunday Date]@row, {Date Column}, 0))

    Then subtract the value from 7 days before this date:

    - INDEX({Hours Column}, MATCH([Sunday Date]@row - 7, {Date Column}, 0))

    For a full formula:

    =INDEX({Hours Column}, MATCH([Sunday Date]@row, {Date Column}, 0)) - INDEX({Hours Column}, MATCH([Sunday Date]@row - 7, {Date Column}, 0))


    Let me know if this works for you!

    Cheers,

    Genevieve

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!