-
Formula for RAG rating based on status and dates
Hi Smartsheet community, I'm after a formula that will produce the different RAG ratings. I'm definining them based on whether they are on schedule and their statuses. Blue is 'Status'="Complete" Red is (open for advisory) 'Status'='Not started' and 'start date' < today and end date is within 14 days OR Status= not started…
-
formula that determines how many working days 2 tasks were in progress at the same time.
I have a Start and End Date for 2 tasks. (Task 1 and Task 2). [Task1 Start], [Task 1 End], [Task 2 Start], [Task 2 End]. I am trying to write a formula that determines how many working days both tasks were in progress at the same time. Any suggestions would be great appreciated. thanks
-
Help with a formula
Hi there, I'm looking for a formula that would look at an Identifier Column and pull back the Amount Financed. But if the Identifier Column is repeated, would only pull back the first instance of the Amount Financed. Here's a snippy of what I want that Quote Value column to look like. But I'd like to be able to use a…
-
Formula continuously updates cell even though values are not changing
Certain combinations of results are continually updating even though the value/result didn't change. Every time I open the sheet, it requires me to save but I didn't change anything. Combinations with issue: "Utility, Telecom, Waste" and "Utility, Telecom" Combination without issue: "Utility, Waste" Services column is…
-
Date Logic in Form
In forms it is not uncommon to have a Start and End date for any reason (mine is for PTO). It would be very helpful if there could be some logic added to Date fields to compare with other Date fields. In my case I need to add logic that prevents a user from entering an End Date that is prior to the Start Date. Today this…
-
How can I append additional text to strings pulled via formula from other cells?
I am using a large function to pull together a list of names based on whether someone has provided an update. The formula that I am am using is basically… =IF(Input from Cell X = "", [Contents from Cell X1], "") + ", " + IF(Input from Cell Y = "", [Contents from Cell Y1], "") + repeat. This works but it gives me lists with…
-
Date value is off of Modified Date is off by 1
Something has changed. I have a formula =[Modified Date]@row that captures the last modified date and keeps it static. I do this as I have rows backed up nightly to another sheet if they were modified. Just using Modified Date doesn't work as the date changes once it's on the new sheet. It's now rounding the date up. The…
-
Dashboard Request: Track Sub-Task Completion Time from Project Start
We have projects with multiple sub-tasks, and we’d like to track how many days it takes to complete each sub-task from the project submission date. Ideally, we’re looking to create a dashboard that shows: Each project The average number of days it takes for sub-tasks to be completed (from project start/submission date)…
-
Fix #INVALID COLUMN VALUE error when using Index Match in a different column type than source sheet
Hello! I am curious if there is a solve to the #INVALID COLUMN VALUE error I get when trying to pull a value from another sheet using Index Match, and both the source sheet and current sheet have different column types. I am currently using the following formula: =IFERROR(INDEX({OChM - Intake - Intake Meeting Date},…
-
Calendar Query - repeating item automation
Hi there Im creating a calendar for my team in SS - some items are repeating Weekly / Monthly / 1st Wednesday etc. How do I automate that rather than having to type each entry individually ? Thanks for your bright ideas 🌟