-
Autofill Date with Dropdown Selection
Hey everyone. I am trying to figure out how to make my sheet autofill today's date when we complete a project. We have a status column and when that column is marked "Completed" I want the Completion Date to autofill to today. I tried this: =IF(Status@row = "Completed", TODAY()) The problem is that it changes the completed…
-
How to get a tally of rows fitting relative date criteria (start date within last 30 days etc)?
Hi team, I have a Smartsheet used to track defects reported for the product I manage, and I need to report on 3 metrics. 2 of these are based on relative dates (sheet includes Start Date and Date Closed columns) and my team hasn't been able to get the formulas parsing properly. I'd appreciate any help or advice! Total…
-
IF AND Formula - Multiple Values
Ok so I answered my first question however I need the following created. The formula works however I need to add two more contract types that are part of the TRUE statement: =IF(AND([Billable Consultant/Developer Hours]@row > [Consultant/Developer SOW Hours]@row, [Contract Type]@row = "Prime"), 1, 0) Need to add…
-
Automated Reminders Based on Approaching Due Date
Hello Community, I am attempting to produce an "if/then" statement within a column of my project management smartsheet in order to create an email automation to remind the assigned user to address the aging task. Using the formula, I should be able to create conditional formatting or email automation workflows that address…
-
Pull information from report to sheet
Hello, I have a template that my team uses for data collection each time we get a new lot of diagnostic tests we need to quality check. In one folder, "data collection forms" there are many sheets, as each lot gets it's own sheet. New lots are tested every day, so the sheets in this folder are constantly being added to. We…
-
Formula Help w/AND
Hello Community! I really struggle with formula syntax. Here is what I'm trying to do. I have a project plan and when certain milestones complete, I want to count the actual "widgets" I completed. My project plan has multiple phases so the completion of each phase adds more widgets to the bucket and I want to reflect that…
-
Formulas for auto creating start time / end time based on duration - Event Runsheet
Hi, I am wanting to create an event runsheet. I have a formula that automatically calculates the duration from start time / End Time but Im having trouble figuring out how to automatically calculate Start Time / End Time from Duration which is more Ideal. I could enter a Start Time & End Time in the parent row and then in…
-
Looking for help with countif formula
I'm having some trouble with a countif formula, looking for some assistance. My goal here is to count the number of time the Created Date is last month and that the service type is "Trade Database - Add Part Number(s)" When I enter the formula below it works great! =IF(MONTH(TODAY()) = 1, COUNTIFS({Genie Archive Created…
-
Calendar View - can't drag tasks when Due Date uses WorkDay function?
My SmartSheet has Task Due Dates that are calculated using the WorkDay function. For example, if a recurring monthly task is due on WorkDay 1, that calculated Task Due Date would become Monday, January 3rd, rather than Saturday, January 1st. Approaching the dates in this way makes it easy for us to rollover the Task List…
-
Formula to pull data from one sheet into a different sheet.
Hello Smartsheets, I am building a sheet that will track an employees attendance of our monthly trainings. Sheet #1 is the raw data, Sheet #2 contains all of the formulas and combines all raw data in a legible way. See photos below. Sheet #1 This sheet contains all raw data received from the attendees of each monthly…