Formulas and Functions

Let the community help you with that tricky formula or function! See how others are utilizing the power of formulas and functions to save them time and create more powerful solutions with Smartsheet.

Discussion List

  • Automatically have End Date initially match Start Date
    In Formulas and Functions
    Is there a way for an End Date to always automatically match to the Start Date, but for me to have the flexibility to then select a different date if needed?
    3 0
    Question 3 views 0 comments Started by Callie Langford
  • Traffic light formula problem with two conditions
    In Formulas and Functions
    Dear I have a column of% Complete (% Complete) and a Task Completion (ENDDATE) I would like to generate the following conditions: 1. Green if 100% of the task is complete and it has been given on the estimated date (which is equal to or l…
    Answered - Pending Review
    13 1
    Answered - Pending Review 13 views 1 comment Most recent by Bassam.M Khalil
  • How to autofill the status
    In Formulas and Functions
    Hello, I need to setup a formula to have the status auto updated/filled whenever the date is entered on "EUDC Date in" column. at the moment "Status" is a drop down menu and we are manually selecting the status. Than…
    17 9
    Accepted Answer 17 views 9 comments Most recent by Bassam.M Khalil
  • Number of duplicates
    In Formulas and Functions
    Hello,  I'm brand new to Smartsheets. Is there a way to find the number of duplicates in a column for a specific value? For example, how many "John Smiths" are in a list of names?  Many thanks, Pltrmgrl 
    534 11
    534 views 11 comments Most recent by Julius Benn Sabeniano
  • How to get minutes using this formula? The output only display 14 instead of 14:30
    In Formulas and Functions
    =VALUE(MID([12 Hour Format]@row, FIND(" ", [12 Hour Format]@row) + 1, FIND(":", [12 Hour Format]@row) - (FIND(" ", [12 Hour Format]@row) + 1))) + IF(FIND("P", [12 Hour Format]@row) > 0, 12) + 6
    Answered - Pending Review
    13 2
    Answered - Pending Review 13 views 2 comments Most recent by Alia AhmadKamal
  • Formulas for Calculating Time
    In Formulas and Functions
    I have decided to create a post that consolidates all of the different time based solutions that I have put together. Some of these solutions can be made more efficient or condensed into fewer but larger formulas. I am still going through …
    4158 59
    4.2K views 59 comments Most recent by Michelle B
  • COUNTIF or COUNTIFS formula for a date column, criteria based off MONTH and YEAR
    In Formulas and Functions
    I am trying to total the number of completed submissions on my Ankeny Repair spreadsheet based off of a specific month from a specific year. Range 4 is the column with the completed dates. My current formula is this... =COUNTIFS(MONTH({Ank…
    519 10
    Accepted Answer 519 views 10 comments Most recent by Sue Hill
  • Current Week and Next Week Formula
    In Formulas and Functions
    Hi, I need help with a formula that will help me determine if a date is the current week or the next week in the calendar. Need the weeks to star on Sundays. I'm using the following two formulas: For Current Week I'm using the following fo…
    Answered - Pending Review
    19 2
    Answered - Pending Review 19 views 2 comments Most recent by Rajul Khandelwal
  • Formulas to identify duplicates
    In Formulas and Functions
    My company is assigning decals to vehicles to monitor our guests. Is there a formula to identify if a license plate has already received a decal if I am looking at my sheet? We will have 2000+ license plates and need this to be a quick gla…
    Answered - Pending Review
    16 1
    Answered - Pending Review 16 views 1 comment Most recent by Andrée Starå
  • Question on IF / AND OR Formula
    In Formulas and Functions
    Hello, I have this formula that works great. I would like to add one criteria to it, however. If a new column (not currently referenced here, which contains a "priority status" (different than "status") that is "hi…
    Answered - Pending Review
    15 1
    Answered - Pending Review 15 views 1 comment Most recent by Andrée Starå