-
Estimate Date Planned against the target date
I need to calculate the planned date against the Go-Live date for each column. The dates are above the columns that should be prior to the Go-Live Target which is 10/31/22. I need to make the actual columns cells yellow for 3 dates against the planned date, red at the planned date. If a date is entered in actual column,…
-
Count time between dates in Weeks/Days
How would I calculate the time between two dates (start and finish) in Weeks and Days "8weeks 4days"
-
Formula help = if date contains TBD, leave blank
Hello! I'm struggling with the 3rd part of my formula. I need to have the formula calculate: A date from the entered work week; be left blank if no work week is entered and leave blank if TBD is listed in the work week column 1 & 2 work using this formula: =IF(ISBLANK([Work Week]@row), "", (DATE(year@row, 1, 1) + 7 *…
-
Formula that updates Blank cells, ignores data when present
Hello everyone! Stumped trying to figure out a formula for our Pre Task Plan Smartsheet. We have a form submission process that users answer questions for their project. If they answer Yes, then they are asked to provide more information on 2 additional questions. However if they answer No, the other 2 questions are hidden…
-
VLookup with multiple references in one cell
Hey all, I'm not even sure what I'm after is possible, but thought I'd ask anyway. We're using a sheet to track users while they are out on test rides. Each model available for test rides has a number assigned to it. When users take a ride, we enter their name and the test ride ID# to identify who is on which (TRACKER). I…
-
Match all columns to complete
I have several columns with dropdown (Complete-Go, Incomplete, TM Approved-Go) and one column that says (Go/No Go). If all columns have "Complete-Go or TM Approved-Go" the column (Go/ No Go) should be "Go" If any column has "Incomplete" the column (Go/no Go) should be "No Go" I appreciate your help very much! Rob
-
How do you set a flag based on status and days within a due date?
I'm trying to create a formula with the following logic: Don't highlight the red flag if the status is N/A or Completed Highlight the red flag If the status is In Progress or Not Started and the due date is <= Today (-3 days) Below is what I'm using, but now none of the flags are turning red (and no error message appears):…
-
How to put a value for each item in dropdown list
I have a dropdown list and I want to put a value for each item (but not in the list): Not Ordered = 0% complete Ready to Order = 1% complete Order Process = 20% Shipped = 50% complete In Country Customs = 80% complete Delivered = 100% complete Cancelled = 100% complete This is to help me to calculate the task % complete…
-
How to include a newly inserted column in an existing formula?
I have a formula at the end of a row that adds everything to the left. I want to insert a new column and have it automatically included in the formula. Excel does this by default, can smartsheets do the same?
-
Need help with a formula to calculate accrued PTO based on # of pay periods
Hello, I am seeking help with a formula to calculate accrued PTO based on the # of pay periods per person each year and if they are full-time. Example 1: Nikki started on 7/1/22 and accrues 3.5 hours each pay period (twice a month) so I need a formula to let me know what she has accrued to date. Example 2: Laura started on…