-
=NETDAYS formula updating in the afternoon
I am using the formula below to count the number of days since an item was approved, to today. For some reason it updates in the afternoon. I believe the update happens at 3pm cst. It makes the data look unreliable because it feeds a lot of metrics. For example, it feeds a metric for # of items opened in the last 7 days.…
-
Automatically enter certain Text in a cell if Date Open > 60 days
I currently have 2 formulas that work in conjunction to look at Acct Open Date and if it's greater than 60 days it populates a column/cell with yes or no (via checkbox) then, looks to a SECOND column/cell and tells the first cell to enter the text that is in the reference cell. It works fine but it relies on me copying and…
-
Set Parent's Projected Date based on Children's Status and Projected Date
I am using several layers of parents + children rows, and I need the duration / projected date to be "TBD" when a child row is "In Progress" or " Not Started." Here's the current situation - when a child row has a date set, it rolls up to the parent, which is inaccurate as a projected end date since there are still items…
-
Formula to Check How Many Boxes are Checked
I have a metric summary sheet that I am building to use as the point of reference for my dashboard. On my source sheet, I have a check box column for "Copy Needed." I want to count how many people requested copy needed when they made their request. So, ultimately, I need a formula that will pull back the total number of…
-
Conditional Dashboard Formating?
I have a sheet with several rows, and those rows may or may not have sales orders. If the row has a sales order then its booked, if it doesn't then it's pipeline. I'd like to have a dashboard pull data from this sheet that adds up all of the rows that contain an SO and also add up all the rows that don't have an Sales…
-
Separate last name, or auto enter "GENERAL" if cell is blank
My smartsheet is autopopulated by a form in which a submitted can enter "referred by". If a full name is entered, I want to separate the last name and first name to other columns so I can group by last name. I have the formula =MID([REFERRED BY]@row, FIND(" ", [REFERRED BY]@row), 20) to pull out the last name. If there was…
-
Need help with a formula for my asset report
I'd like to know what formula would give me the desired output from data within my asset inventory sheet. I'd like this output to be populated in my report. See example
-
Countif a dropdown was selected before a certain date
In our project management process, we have a drop down column for an employee to note where a project is in the process, ending with "Completed". I want to do a count for how many of these projects were completed in our last fiscal year. I'm looking for a formula to say "if "completed" was selected before 7/1/2020, then…
-
IF/AND
Hello, There may be a more efficient way to solve this so please feel free to suggest an alternate way. I have a column in my sheet that collects the time/date a form is submitted using the "Created - Date" field option . I need to set up an automation that would be triggered by the time the form was received AND the day…
-
Find the Percentage of the Total
Hi! I have 4 columns that I have added together in the 5th, Total column (using =sum formula). Each of the 4 columns is a single dropdown select (values: 1-10). I want to translate the Total into a percent in the Percentage column, so I thought if I took the total value and divided by 40 (the max amount) in the % column, I…