-
Create a fiscal year formula
I include fiscal year on most of my sheets. Our fiscal year starts July 1, so I write a formula such as FY = if(Month(date)<7, Year(date), Year(date)+1). Would it be possible to create a FY formula in Smartsheet that returns the FY when provided a date and the FY start? FY(date,FYstart) as FY( mm/dd/yyyy, fymm/fydd )? Or,…
-
Function Question
If a cell is check marked "✓" then I want a cell next to it to show the value of another cell. Then I want all subsequent cells underneath to run a value of that cell plus the value of the previous cell(s) only when checked. How would I create the formula for that please? For example if N3 is "✓" then M3 equals value of C3…
-
Why Can't Gantt Charts Have A Formula In The Start/End Date Columns?
I'm trying to create a Gantt Chart on a document where the End Date field is a column formula; it being a formula is pretty much non-negotiable. The formula reports a date from the 'Requested Due Date' column unless a new date is provided in the 'Extended Due Date' column. This is done to ensure that the person using the…
-
How can I pull data from different sheets with vlookups/index formula in one column?
In an easy way to explain what i want to achieve: I have 3 smartsheet files: 1- Tops ( contains all tops with price) 2 - Bottoms ( contains all bottoms with price) 3 - Master Sheet ( contains all tops and bottoms) I want to set up a price column in the master sheet links to the tops or bottoms sheet based on the item and…
-
MIN/MAX with Date and Time (Modified Date Column)
Hi all, This should be straightforward but I am stumped! How do I get the earliest (or latest) value including time from a Modified Date system column? e.g. I can get the date if I define my output column as type Date and use a simple MIN/MAX but this means the time is ignored. I thought about breaking the time and the…
-
Auto Generate a Number based on Column not being blank.
Hello, I am trying to get an auto generated number in Auto Number column. I cannot use auto number given that i have conditions to it. I want my Autonumber column to have a formula where it will create a sequential number based on the Hold Issuance date not being blank. I only want it to autonumber if the hold issuance…
-
IF OR/AND Function - Considering Dates (Future, Past etc)
Hi Team - I have a bit of a doozy.. at least it is for me. I have a sheet that needs a status populated considering 2 different date columns. If either "Expiry Date" or "Expiry Date 2" is in the past we need a "Red" status If either "Expiry Date" or "Expiry Date 2" columns are within the next 30 days "Yellow" Status If…
-
Formula to pull the Fiscal Year from a Launch date
Hello! I'm looking to pull the Fiscal Year in a cell based on the launch date in a different cell. Our FY starts on 04/01 of the current and ends on 03/31 of the following year. I need the formula to determine if the FY is the current year, the prior year, or the next year based on the launch date. For example, 04/25/2024…
-
IF ERROR help for a nested IF AVG formula
Hi there! I have an IF followed by AVG formula that is working ok- looks at multiple columns and averages them (frankly it is problematic, because it returns an average even when not all 6 columns are filled out, help? Is there a way for it to only calculate the average once all 6 columns have a value added? Honestly if…
-
Formula for automation help
Hello Smartsheet gurus! I am trying to automate a step-challenge for teams at work. Team members submit personal step counts via webform to Sheet 1. Sheet 1-Column titles: Team Name (drop down), Team Member (free text), Steps Entered (free text) Numbers submitted are automatically summed on another sheet, as an ongoing…