-
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…
-
#UNPARSEABLE for Column "IF" Formula
I get an #UNPARSEABLE error for the following formula and am not sure what I'm doing wrong: =if([Destination]@row] = [Source]@row, [Destination ID #]@row, "") Where the destination = source, the destination ID should be entered. Otherwise, I want to leave the cell blank so that someone can manually enter the ID #.
-
Smartsheet not recognizing a number value
I have a sheet with item numbers and Dates when a purchase order is due. Some items have multiple purchase orders outstanding, so I have a MIN(COLLECT statement to find the earliest of the open PO dates for that item. What has me baffled is that for "Item" that has a six digit number, it doesn't return any value. If it has…
-
JOIN Muliple values dropdown menu
Hi, I have a simple JOIN formula to combine the cells on the right into a muli value dropdown menu column. However, for some reason, it will only show unique value and sometimes it wouldn't show it in order. Column "Join Formula" is a simple text/number column, with the same formula as "Join formula w/ muli dropdown". It…
-
looking for help with a formula that uses one "text", and one "match" or "has" condition.
the closest I have gotten to getting some results is using: =IF(AND(CONTAINS("Q8", {Expenditure Report Collection Range1}), HAS({Expenditure Report Collection Range 2}, [Primary Column]@row)), "ok","missing") maybe I don't understand order of operations but it's giving me a "ok" on a cell that should be missing. I need to…
-
Adding 1 more condition to AVG () with Collect() formula
I have successfully used the below to average the aging for help tickets that were reported between 2 dates. I need to add one more condition, so that it only provides the average if the severity of that ticket is Urgent or High (using @row). I cannot figure out where/how to add this third condition. =AVG(COLLECT({Closed…