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!

Find more posts tagged with

Accepted answers

All comments

Genevieve P.

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

MichaelB

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.

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

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.