Formulas and Functions

Archives for October 2022

Date format in automated emails
Hi there, I've set up multiple automated emails as part of the workflow process and have just noticed that the dates contained in the automated emails are in the incorrect format, despite me setting my regional preference in the settings. I have the dates set up in my settings for Australia format (i.e. DD/MM/YY) and this…
Formula challenge
I want to remove the subjectivity in reporting on project domains. The aim is to use a formula to determine the 'summary rating' of a domain eg risk. This will be based on the content of the risk log (sheet). Community help is requested on the formula. The table below maps the thinking to develop the formula. I think it…
RAG indicator at parent level Vs Child
Hi All, I need help on a formula. In my current schedule the RAG is determined by % complete of task, desired% as on today and low tolerance. Here the parent level is indicating RED although child is in green and blue but no RED. Probably due the Desired % is more than the actual % but the actual % for child task is more…
Max(Collect()) To pull in the most recent entry
Hi! I am attempting to use MAX(COLLECT()) to pull in the most recent entry from a manually maintain status log. If the date in "Week off" column on the log is the most recent date and the Project name matches the name on the row in the "Data Sheet" then I want to populate the entries in the "Weekly Highlight" column.…
If Date is between Today and 4 Days from Today = YELLOW
I've been looking at the different threads for an example that I can use for this formula but could not find anything that helps. What I'm looking for is if the END DATE is less than today, RED. If its greater than today and less than 4 days in the future, YELLOW, otherwise GREEN. I'm getting YELLOW for any date <= TODAY.…
Automated formula for dates
Hi All, We have internal learning modules, and once completed you are issued a 'badge'. I want to create a report that will tell me each month how many badges were issued of each badge for that month and then the total for the month. i.e. Dec 2021 10 IT badges, 5 HSE, 6 Ops badges = 21 for December I have created a new…
Formula to calculate day from a series of dates and times
Hi All, This might be a long shot but is there a formula to search and find a multiple entries of a job number then look for the date column and calculate the series of dates for start and finish time and also include the in and out times to create a Gantt chart? This is all data that will be entered into a separate…
Today Forumula Question
So I'm still learning formula's. I'm trying to create a formula in my sheet summary. I'm looking for a count of end dates thats within the next 4 days. from today. Here is my formula: =COUNTIF([End Date]:[End Date], >TODAY(4))
Would This Be an Index/Match Formula?
So I have a workspace for a client install we're doing. I have another workspace where I manage our company employee contacts. For the client install workspace, I'd like to create a widget to access the contact info based on "assigned to" in the client intake sheet. Is this an Index/Match formula where I need to create a…
Creating a formula to track number of days but with a catch
So I have been tasked with creating a formula that tracks the number of days an element is "No". However, there are multiple forms submitted each day so I do not want to count the number of times a "No" is selected. It needs to be a formula that counts the consecutive days but then resets to 0 if the element has a "Yes"…

Help Article Resources

Create and edit formulas in Smartsheet

Formula combinations for cross sheet references

Smartsheet functions list

Upcoming Events

Brandfolder Collaborator User Guide
Get ready to seamlessly upload and organize content within your Brandfolder! This on-demand session is designed to help you become a confident contributor and collaborator, ensuring your team has the right assets at the right time. What You’ll Learn: Maximize Features in the Asset Modal: Learn to navigate the asset modal,…
Brandfolder Admin User Training
Managing digital assets doesn’t have to be complicated. Join us for this two-part, on-demand webinar series designed to give Brandfolder administrators the confidence and skills to create smooth, efficient workflows. Whether you’re just getting started or ready to optimize your setup, these sessions will help you unlock…
Smartsheet Customer Success Onboarding (Japan)
Smartsheet カスタマーサクセスオンボーディングは、Smartsheet についての基本を学びたい方向けに毎週開催しているインストラクター主導のトレーニングコースです。ウェビナーは、始めるための基本、共同作業、およびさまざまな方法での作業の視覚化の基本など、Smartsheet のご利用を開始するために必要なすべてを網羅しています。

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!

Trending in Formulas and Functions

COUNTIFS formula across sheets
Hi all I've been using Sheet Summary's to calculate some data which seems to work quite well. For instance this is an example of a Summary that is working fine. (Its an ISO27001 audit tracker sheet) =COUNTIFS(Finding:Finding, "Opportunity for Improvement", Status:Status, <>"COMPLETE", [Agreed closure date]:[Agreed closure…
Need Help With Date Calculation Formula
Hello, I have 2 date columns. I put a test sheet together for a visual reference. The Date 2 column needs a formula to calculate whichever date 39-45 days out is a Friday. Any help would be greatly appreciated.
Formula help for INDEX MATCH. Or is it INDEX COLLECT?!
I have a holiday sheet for Team Members to log their holidays: Assigned To, Start and Finish Date. A formula has been created to flag the status of when they are ‘On Holiday?’: eg, ‘Holiday Complete, ‘On Holiday’ and ‘Holiday Booked’. Based on this status, an ‘Availability’ symbol also appears Id like to use this…

Formulas and Functions

Discussion List

Help Article Resources