-
NETWORKDAYS/IF/ISBLANK - Can you subtract from the returned day count number?
Hello! I am having a hard time figuring out a formula that will count the NETWORKDAYS between a [Start Date]51 and the end date [Escalation Completed Date]29 that will count as TODAY() if the end date cell [Escalation Completed Date]29 is blank AND if the [Days On-Hold]29 is NOT blank subtract the number in that cell from…
-
Creating a formula to determine cost based on different factors
Hello! I am trying to find a formula. Basically, in column A I have a number (example-1500) and I am looking to find a formula that will calculate the cost based on range. I.E. if the number in Column A is 0-1500...multiple by 5. If the number in Column A is 1501-2000, multiple by 10. Can anyone guide me in the right…
-
Formula for changing symbol colors
I created a column that changes symbol colors depending on the start date column. The below formula I put together after reading through some other posts within this community works alright except for the bit to have the symbol change to gray when the start date is today or past. What am I missing? =IF([Start Date…
-
Progress Bar Automation between 2 Different Sheets
Hi to all you brilliant Smartsheet Wizards! 🧙♂️🧙♀️ I saw a question on here regarding how to "automate a progress bar" and I thought that is perfect for my tracking: Automate the Progress Bar — Smartsheet Community My problem: I want the progress bar in Sheet A under "milestones". But I want it to source the data from…
-
Cross-referencing sheets based on date and month of value
Hello, I'm attempting to create a cross-sheet formula that inserts a date onto a target sheet depending on whether the month and date on the source sheet are between certain date ranges. More specifically, on my target sheet (let's call it sheet B), I have columns titled Spring Meeting, Summer Meeting, and Fall Meeting.…
-
How to graph a range of dates when only given a start and finish
I have a sheet where I have individual tasks each with their own start and finish dates that can overlap. They each have a certain # of people assigned to the tasks per day. Essentially, it looks like start: 5/15/23 | end: 6/25/23 | #ofPPL/Day: 5 and for each day in between those dates there will be 5 people. The goal is…
-
At risk flag automated
Hi all, I would like to automate my 'At Risk column' via a formula. The flag should be red if Due date is in the past AND if Status is NOT 'completed' 'or empty) I am using this formula: '=IF(Date@row < TODAY(); Date@row <> ""; 1)', but it is not working correctly. What is wrong with this?
-
Help with IF / CONTAINS Function
Hi! I am trying to write a formula that will look at a description column, see if it contains a specific string of text, then return a list of adjacent cells where the formula is true. Said another way, I want to type a string into a cell (A1), look at the description column on another sheet, then return a list in column B…
-
Counting records
Hi, I'm creating a form to capture data that includes the name of the person completing the form. The info on the form will then be reviewed and categorised. What I want to do is count up the number of times a named person appears and also do this by the various categories. The problem I have is that, whist I know the…
-
Remove comma from extracted text
Hi, I am using this formula to extract the last name from a name but it includes the comma. =LEFT(Name@row, FIND(",", Name@row)) How do I remove the comma? I tried =LEFT(Name@row, FIND(",", Name@row), - ",") but that didn't work. Thanks, C'loni
