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. Employee Admin
The way I would do this is to use theWEEKNUMBER Function. Try adding a helper column with aColumn Formulaof:
= WEEKNUMBER([日期时间报道]@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
我需要捕获多少小时被麻吉了ne 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. Employee Admin
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
Categories
Check out theFormula Handbook template!
I figured it out! Updated formula to get the 2 decimal places as well.<\/p>
=\"We are at \" + IFERROR(ROUND([% closed rate]@row * 100, 2), \"//www.santa-greenland.com/community/discussion/comment/325291/\") + \"% closed rate on ticket status for the month of \" + Month@row<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question"},"bookmarked":false,"unread":false,"category":{"categoryID":322,"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions","allowedDiscussionTypes":[]},"reactions":[{"tagID":3,"urlcode":"Promote","name":"Promote","class":"Positive","hasReacted":false,"reactionValue":5,"count":0},{"tagID":5,"urlcode":"Insightful","name":"Insightful","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":11,"urlcode":"Up","name":"Vote Up","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":13,"urlcode":"Awesome","name":"Awesome","class":"Positive","hasReacted":false,"reactionValue":1,"count":0}],"tags":[]},{"discussionID":108861,"type":"question","name":"IF\/AND Formula","excerpt":"Formulas are the bane of my existence and I really need to take a class! Today I'm trying to set up a formula to throw a flag when 2 conditions are met. I want a red ball when I have not received an invoice and the invoice due date is within 30 days. I know I'm close since I've gone from \"unparsable\" to \"incorrect…","snippet":"Formulas are the bane of my existence and I really need to take a class! Today I'm trying to set up a formula to throw a flag when 2 conditions are met. I want a red ball when I…","categoryID":322,"dateInserted":"2023-08-11T16:27:44+00:00","dateUpdated":null,"dateLastComment":"2023-08-11T17:49:45+00:00","insertUserID":120231,"insertUser":{"userID":120231,"name":"Pamela Wagner","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Pamela%20Wagner","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-08-11T17:47:38+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"updateUserID":null,"lastUserID":120231,"lastUser":{"userID":120231,"name":"Pamela Wagner","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Pamela%20Wagner","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-08-11T17:47:38+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":4,"countViews":49,"score":null,"hot":3383549849,"url":"https:\/\/community.smartsheet.com\/discussion\/108861\/if-and-formula","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/108861\/if-and-formula","format":"Rich","lastPost":{"discussionID":108861,"commentID":390268,"name":"Re: IF\/AND Formula","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/390268#Comment_390268","dateInserted":"2023-08-11T17:49:45+00:00","insertUserID":120231,"insertUser":{"userID":120231,"name":"Pamela Wagner","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Pamela%20Wagner","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-08-11T17:47:38+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"}},"breadcrumbs":[{"name":"Home","url":"https:\/\/community.smartsheet.com\/"},{"name":"Get Help","url":"https:\/\/community.smartsheet.com\/categories\/get-help"},{"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions"}],"groupID":null,"statusID":3,"attributes":{"question":{"status":"accepted","dateAccepted":"2023-08-11T17:48:48+00:00","dateAnswered":"2023-08-11T17:12:43+00:00","acceptedAnswers":[{"commentID":390261,"body":"
You were, indeed, very close.<\/p>
=IF([Invoice Received?]@row = 0, IF(AND([Renewal Date]@row >= TODAY(), [Renewal Date]@row <= TODAY(+30)), \"Red\"))<\/p>"},{"commentID":390264,"body":"