-
Tenure - Years and Months Formula - help!
Hi All! I know there have been many posts re: figuring out tenure, but I am super new to spreadsheets and some of the community posts keep losing me with the round ups, @rows etc, I was wondering if someone might be able to explain with my own row examples - how to get years and months of tenure using 1. Start Date and 2.…
-
Help with formula for schedule health
Hello, looking for some help with calculating schedule health. I have added a symbols column (Green, yellow, red, gray circles) called schedule health to schedule. I have started creating a formula to calculate the schedule health for each row. This is what I have based the formula on: On Track = Green. Occurs when ‘%…
-
If/And/OR to give Status based on 2 Variables.
Hi Folks, Working on an If/AND/OR thing that I can't seem to work through. Which is frustrating because it's rather straightforward. If Col 1, and Col 1 are both "No" then I want to output "Open" into the Status Column. If Col 1, and Col 2 are both "Yes" then I want to output "Closed" into the Status Column If Col 1, and…
-
AVG(COLLECT) formula - need help please
This is what I have working but it's not what I want because it only works for an exact match for date. =AVG(COLLECT([Column5]1:[Column5]4, [Column7]1:[Column7]4, DATE(2022, 10, 4))) Column 7 is a DATE column and I want to average the numbers in Column 5 only if the date range is between Oct 1 to Oct 31. It works for a…
-
Countifs with Find Newbie Question
Hi all, New to Smartsheets. I've read several of the post on this topic but can't get it to work on my side. I have a metrics sheet with everyone's name on it...trying to reference a project sheet "team members" column which list all the different people on a project. I want to show a total next to each person to show how…
-
#UNPARSEABLE with multiple IF statements
Hi, I am trying to create TEXT depending on some conditional information. I want to display text RED if my materials clear date is less than 7 days from my due date, display text YELLOW if my materials clear date is less than 14 days (but greater than 7 days), and if my materials clear date is greater than 15 days, leave…
-
IF AND RYG Formula
Hello, I've created the following formula and one part is not working: the RYG Red balls are not displaying when they should and instead are displaying Yellow. Can you help me identify why this is happening (my IF/AND part of the formula does not seem to be working)? Thank you! * If the Finish (date) field is blank, the…
-
Assistance - Adding a year criteria to SUMIF formula.
The below formula has been working great but I now was to add a criteria that the date column also needs to be in year 2022. I am not sure how best to go about doing this. Any help would be much appreciated. =SUMIF({Sheet - THE Master Range 1}, CONTAINS("E - Quote Accepted", @cell), {Sheet - THE Master Range 2}) +…
-
#UNPARSEABLE with multiple IF statements
Hi, I am trying to create TEXT depending on some conditional information. I want to display text RED if my materials clear date is less than 7 days from my due and display text YELLOW if my materials clear date is less than 14 days (but greater than 7 days) and if my materials clear date is greater than 15 days, leave the…
-
Duplicate Formula
I don't know if a formula will work, but it is worth a shot. I am looking for a formula that will find duplicate rows based on two columns' values. the formula needs to find when the Title has identical numbers while both of them have a current revision value of "Yes". The sheet gets updated daily from an excel report…