-
Calculating a date in the future
My use case: I receive reports that need to have an SLA tracked. From the date I receive the report, I need to calculate the due date. If a report has a severity of Critical I need to calculate the report date +60 days, if High, the report date +90 days. I have a severity column with CRITICAL, HIGH, MEDIUM & LOW, I want to…
-
MAX COLLECT Formula
I have one table for shipping (Production Priorities) : And another table for Project Revenue Recognition (Revenue Recognition Forecast): I would like to write a formula to auto-populate the max ship date for each project. =MAX(COLLECT({Production Priorities Range 1}:{Production Priorities Range 1}, {Production Priorities…
-
COUNTIF HELP
I'm summarising and want to count a Project Status columns Have a sheet per colleague and want to do formula that would add up how many On HOLD projects there in total across all of those colleague sheets?
-
Adding Months based on Calendar Days not individual days
I am looking for a formula that adds months to a date and keeps the "x" day of the month. Meaning if I am adding 3 months from February 1st, 2021, it would return May 1st, 2021 vs. just adding 90 days to February 1st and returning May 2nd, 2021.
-
SUM and TODAY() function
Hello, I am building a formula to give me the SUM of the # of spots for dates called within the last 14 days. I was able to build a formula using the TODAY function: =SUMIF({# of Spots}, <TODAY(), AND({Date Calleds}, <TODAY())) However this formula returns a #INVALID DATA TYPE error. I added a number to the TODAY function,…
-
INDEX and MATCH with CHILDREN across two sheets.
Hi All, I'm having some trouble using INDEX and MATCH together to pull data from cells on one sheet across to another. I'm making an assumption that this is the best method however if I'm mistaken, please advise. I have a situation similar to the below where "MainName" is a Project Name and data beneath it is it's child.…
-
Index Match Max Date Formula
I have one table for shipping : And another table for Project Closeout: I would like to write a formula to auto-populate the max ship date for each project.
-
COLUMN AVERAGES WITH EMPTY CELLS
I'm trying to calculate our hospital ward census (COVID!!!), however on some days the column contains an empty cell /no value. I'm using the following formula, but to no avail. Getting an error message. Any help is greatly appreciated! =AVG(COLLECT($Count:$Count, $Count:$Count, ISNUMBER(@cell))) Stay safe.
-
Sum of Totals by area and Date
I am trying to set up a formula that does the following: If in Work Log A, the Plant is X and the date range is between 10-1-2020 and now, what is the total cost of this time range for this sheet? I also want to break down by monthly range: If in Work Log A the plant is X and the date range is between 1-1-2021 and…
-
Validate an email address in a contact list
Hello, We have a situation where we have two employees with the same name, "Test User", and they have the following email addresses: test.user@smartsheet.com test.user2@smartsheet.com (Please notice the "2" in this email.) We use a form to populate our sheet and the form user is required to type(paste) the email address…