Formulas and Functions

Archives for November 2019

Adding column of summary date values
I have a "Date Completed" column in my list that I would like to roll up like Start and Due Date do with sub-tasks in the hierarchy. I have been unable to figure it out with formulas. So, I am looking for the last date entered in "Date Completed" of a sub-task to auto fill that same date into the highest level of the task.
Formula for Actual duration excluding duplication
Hi Community, I am trying to calculate the actual duration starting from a list of Start and End dates. What I am trying to achieve is to exclude duration overlaps in order to get the actual effort days only, but including not contiguous time ranges. Please see example: Start: 01/10/2019 End: 04/10/2019 = 4 working days…
'Receive Copy of Form via Email' Checkbox: Default to Checked?
Good morning and hoping for help on a quick question, re: the feature to include a 'Receive a Copy of Your Submission via Email' checkbox at the end of a form. My team and I are designing forms to gather information from our end users, and would like to use a 'Receive a Copy' checkbox at the end of each form, to enable…
IF / OR formula help
Hi community, hope somebody may be able to help. I am trying to link a % of completion column with a progress bar. Formula is as follows: =IF([% complete]1 = "0", "Empty", IF(OR([% complete]1 > "0", [% complete]1 <= "0.25"), "Quarter", IF(OR([% complete]1 > "0.25", [% complete]1 <= "0.5"), "Half", IF(OR([% complete]1 >…
Formula to display multiple rows
Hi, I am looking for a formula that would allow me to display a list of projects based on a client. Ideally, what I want is to have a dropdown column with a list of all the clients. When a client is selected then a list of their projects will display. This sheet would link to a master sheet of all clients and all projects.…

Help Article Resources

Create and edit formulas in Smartsheet

Formula combinations for cross sheet references

Smartsheet functions list

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!

Trending in Formulas and Functions

How to Create Time Duration
I have a report where I need a formula to figure out time duration. For example, employee starts at 8:00 am and ends at 9:00 am, I need the next column to tell me 1.00 to indicate he worked 1 hour. I will be using military time throughout this sheet.
#BLOCKED / Auto-Save Issue in Project Billing Forecast Report
We are still facing a critical #BLOCKED / Invalid Value issue in our Project Billing Forecast Report. 📌 Current behavior: In the underlying Sheets, formulas (e.g., Total Billed This Period, To Date Billed) initially show #BLOCKED/Invalid Value, then refresh to correct numbers. However, the Sheet does not auto-save this…
Formula for Resource Type, Resource Name and Year
Hello - We have set up a sheet to pull from a Master document to include Resource Type, Resource Name and Year. The Year is from a Helper column. Can someone review the current formula we have to identify where we went wrong to no pull "zero" as the result vs the actual number. Before adding in the Help Year and Reference,…

Formulas and Functions

Discussion List

Help Article Resources