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

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!

Trending in Formulas and Functions

Auto Populate 10 rows with todays date each day
Is there a way to automatically add today's date to 15 new rows each day without deleting previous dates? This would let us track pull the 15 daily task items that need to be completed each day.
Getting Unparseable error using IF function
Hello, I am trying to use the IF formula to perform a date calculation based on a value selected in another column but keep getting UNPARSEABLE error. Here is the formula that I have used. I have also pasted the screenshot for reference. Can anyone help me out on this? =IF([SLA]@row = "Standard",[Feasibility_Start…
Attachments not sending in Email Alert
I've had a sheet set up where once the form is filled out an automation will create a PDF and then from there is will send out an email alert to the designated email box. The alert includes and documents that were uploaded during the form being filled out and the PDF that the Smartsheet creates. Its been doing this fine…

Formulas and Functions

Discussion List

Help Article Resources