-
IF function to return value based on dates
I am trying to get a value when I put in a particular date If a person puts a date in the Target completion field, I want to return a Sprint in the Sprint field. I dont know if I can use the month or does it have to be between? For example =IF([Target Completion Date]@row) is between 8/1/2024 and 8/31/2024 then return…
-
Issue when Converting to Column Formula - #Invalid Data Type
I am working on a Smartsheet to track invoices for contractors and automatically update how much I need to accrue at the end of the month based on the contractor and average price. Each row in my sheet represents an invoice with details such as the contractor's name, invoice date, and price. I need to calculate the total…
-
Looking to COUNTIF with an OR function for a cross sheet formula.
Having trouble with the last part - the Status column is a dropdown with 5 options. I want it to count if it meets all of the entity, type of request and then if the Status is Ready for Approval OR On Hold. The problem with my formula is after the OR part. Anyone have some advice? =COUNTIFS({Entity}, [A]109, {Type of…
-
Follow-up email, IF no input is entered within timeframe
Hi everyone, I am currently working on a Smartsheet solution to support a compliance declaration process. The process is straightforward: Prior to every board meeting, a form is sent out to departments where they need to answer a YES/NO question confirming they are/aren't adhering to any applicable compliance regulations…
-
How to add if cell is not blank then...or if cell is blank then leave blank?
Thank you in advance. I have the formula working that I need which is this: =IF([PO Amount]@row > 100000, "Level 3", IF([PO Amount]@row > 50000, "Level 2", IF([PO Amount]@row <= 50000, "Level 1"))) But I need to add text that indicates if the cell is blank than leave blank or if it is not blank than the formula above. This…
-
7 AM shift start and Lunch time
Currently Smartsheet Project uses a default 8 AM time as the start date for each day in a project schedule. Is there a way to change that to 7 AM? I am stuck in creating a formula for that. Also, is there a way to take out the one hour lunch that is automatically calculated in a schedule?
-
Using IF formula to update status based on % complete column
Hi all - Need some help with an IF formula Goal: status updates automatically to "Not Started" (when % complete is 0), "In Progress" (when % complete is >0 but <1) or "Complete" (when % complete is 1) based on value entered in "% Complete Column" I've started here, and this formula works; but when I include additional…
-
Two column types in one column work-around - #DATE EXPECTED error
Hi, I'm in the process of transferring a spreadsheet into smartsheet that splits costs by months, and I've ran into a #DATE EXPECTED error for my formula. I can't change the column type as the formula itself requires the cell above to be a date. Does anyone know if there's any work-around? Here's the formula:…
-
How can i calculate the % Complete at a sub-parent level based on multiple criteria?
Hello and thanks in advance for any assistance. I have spent a ton of time attempting to solve and am tossing up the flag. I have a sheet that needs minimal column formulas due to the line/cell count potentially hitting the base reference limits. I have an overall % complete being calculated on the sheet itself already.…
-
Can't Set Column Formula for Duration - Work Around Available?
I have several sheets that are tracking the time a field employee is working at a specific location. This is determined by the Current Date minus Start Date to give me the number of days. I set the output column to be a Duration column so that it would automatically input the "d" at the end. For my purposes, this needs to…
