-
Counting days of the week excluding Sunday & Holidays
I am trying to count the number of days over a period of time that include Saturdays, but exclude Sundays and holidays. Is there a known formula for that? This is a formula I found for excel but isn't working in SS: =SUM(INT((WEEKDAY([LOAD IN START]@row - {2,3,4,5,6,7}) + [LOAD IN END]@row - [LOAD IN START]@row) / 7))
-
Proof Workflow
Hello, Is there a way to create an automation to email a final Proof? I need Smartsheet to automatically email a Proof once the Proof has been marked as completed. Additionally, I need it to send the proof to a group of emails which would be a contact column in my sheet. Currently, we have to manually download and then…
-
looking for a column formula that will populate a value based on the values of two other columns
Hello, I have a column that calculate the health color of a task (green, yellow, red, grey) using a formula and another column where the task manager manually input the color he believes is the actual health of the task. I would like to have a third column (which we will use for reporting) that will shown the value of the…
-
Can you use a formula such as IF that would allow me to manually input a number in the field?
My formula is =IF(PROPOSAL@row = "NO", "N/A", IF(PROPOSAL@row = "YES" ... this is where I would like to leave the field blank for a manual input. If that's not an option, if "YES" what would I use to point to another column to bring in the quote number as this is not working: =IF(PROPOSAL@row = "NO", "N/A", IF(PROPOSAL@row…
-
Using COUNTIFS Formula
Is there a way to use the COUNTIFS function (or another function) like the SUMIFS? I have this one working perfectly: =SUMIFS({OM Book Date}, {OM Total Sell}, AND(@cell >= DATE(2023, 6, 1), @cell <= DATE(2023, 6, 30))) Now I need it to count OM Total Sell (some are blank, I need it to count only the ones that have numbers…
-
IF, Contains, etc. Formula Help
Hello, I am trying to come up with a formula to make my automations easier. Basically, I have a dropdown column with 35 different options (single-select). If a person chooses a certain option, I need to notify a certain approver (1 of 5 people). What would be the best way to set-up a formula for this? Will I need to write…
-
Can you add a hyperlink to a cell that's linked in from another sheet?
I'm working on a tracker & we're basically trying to find ways to combine columns. Currently we have a Sheet Link column and a Revenue column. We manually add the hyperlink to the Sheet Link column while the Revenue column links in from another sheet via formula (based on a unique ID in another column). Our goal is to…
-
Adding 5 years to a date. How do I avoid getting #invalid data when the source date is blank?
Hello Smartsheet Community, I am trying to have a formula add 5 years to a date in a column, but when the source date column is blank/has not date in it - it returns #INVALID DATA TYPE. I would like the formula to look at the latest install date and add 5 years to it, and if its blank return nothing. How can I adjust my…
-
IF cell contains specific letter then return value
I'd like to return a value of 120/208 in Service Volts for Xfmr# ending in E value of 277/480 for Xfmr# ending in X value of 120/240 for Xfmr# ending with no letter Thanks
-
Formula to collect the first and last values from children rows, but only if distinct.
Hello Community People, I'm working to create a report that will show a summary of the location where a person will be each day. The source sheet is set up with parent rows listing the date and child rows showing all the events scheduled for that date, and there is a single drop-down "Location" column to select the city in…