-
adding dates based on quarter
Hiya, I am trying to create a formula to add specific dates based on the selected Quarter. e.g., I select 'Y3Q1' in the Quarter column and it outputs '9/1/2023' in the Date column. I thought I could do this with IF statements but its a no-go: IF([Column13]@row= "Y1Q1 " , " 9/1/21 " , IF ( [Column13]@row= " Y1Q2 " , "…
-
Health Status Formula
Hi! Currently, I have a formula in place to indicate R,Y,G Health based on the Start/Due Date. Is it possible to add an =OR function so that if "Status" column is switched to complete the Health will indicate green. Sorry .. very new to this.
-
If price above/below number of cents...
I'm new to SmartSheet and am struggling to create a formula for the following: If number ends in 0-29, round DOWN to nearest 0.95, (ex. FLOOR(A1,1)-0.05). Otherwise, if number ends in 30-99 cents, round UP to nearest 0.95 (ex. CEILING(A1,1)-0.05). ie: $59.29 rounds to $58.95. $59.30 rounds to $59.95 Can anyone help me put…
-
Slot scheduling conflict within a date range
Hello Smartsheet Masters, I am making a scheduler sheet for equipment slots. The sheet contains these columns: Conflict (Checkbox), Row (Dropdown list), Slot (Dropdown list, multi-select enabled), Start Date, End Date. I would like the Conflict cell to be automatically flagged if any one or more slots have already been…
-
How Find and Replace Contacts - Need change users from personal email to work email
Is there away to easily find and replace contacts in column? All of our users are currently entered as a contact with their name and personal email and we need to change all of the users to their new work contact. For example I need to update an entire column worth of data and change Mike Stone xxx@gmail.com to Mike Stone…
-
COUNTIF + MATCH? between multiple sheets
Hello, I'm needing help with another formula. This is my objective: Match the [Location ID#] between Sheets A & B. Sheet "A" contains one row per location. Sheet "B" contains multiple rows per location. Then, count the number of "Yes" values in Sheet B for each location and return the count total to Sheet A for each…
-
How do I amend this formula
What should this formula look like if I also want it to ignore any tasks that do not have an end date? =IF(AND([End Date]8 < TODAY(), NOT(Status8 = "Complete")), 1, 0) Its template formula from a project template set that surfaces at risk tasks to the roll up dashboard. IE those where the end date is i the past and the…
-
Conditional Color
Hi all! How do I create a formula if the result is between 1.00-1.10 it will turn green and if all others - it will turn red? It will have to be added to this current formula because the cell uses it to calculate the average. =([1st Patient H/L]@row + [2nd Patient H/L]@row + [3rd Patient H/L]@row) / 3 Thank you!!
-
Need help getting to 1.year and 2. month, 12 months ago
I am trying to setup a rolling 12 month helper sheet, but can't figure out the formula to see what the year was 12 months ago, what the month was 12 months ago. Thanks for your suggestions.
-
Index(Collect) - Need to pull a list of projects done in 2022
I have a datasheet with a list of closed projects in 2022. I need to pull from that sheet only the 2022 closed projects that occurred 12 months ago from today. I have a helper sheet, that shows 2022 as the year 12 months ago (manually entering that) and the month 12 months ago (also manually entering that). now need to…
