-
Status should = "Pending" if the cell is null
I have a sheet that looks for errors in a field, then populates a status if it finds those errors. For example: =IF(LEN(Body@row) < 1, "RED", IF(OR(CONTAINS("Rejected: 0", Body@row)) = true, "Green", "Yellow")) However, if the cell is null, it reads "#INVALID VALUE". In addition to the formula above, I would like to…
-
Week day
Hi good morning, I would like to know how I can get the day of the week in full. Is there any formula? I used the weekday but juts give me the number. Thanks
-
Trying to get some formulas to work to make a month timeline
I have a number of sheets that I am relaying on using this timeline sheet for. This is for a commission workplace and individual commissions. In the core sheet, I track the date of sale. Which that gives me a YEAR / Month. Then, I want to assign it a Current Month, Last month, 2 months ago, and so on. My goal was to…
-
What is the correct formula to count a category type with specific date ranges?
Hi! We are trying to count media types (per each specific media category) based on specific completion dates (01/01/2023 through 03/31/2023). What formula should we use to make this work? Any help is much appreciated! We have tried this one without luck: =COUNTIFS({Media Type}, = "Design", {Completion Date}, >= DATE(2023,…
-
Variable in Cross Sheet Reference
Hi All I have created 5 different cross sheet references, that refer to a range in 5 different sheets, these are named: {Year1} {Year2} {Year3} {Year4} {Year5} On a separate sheet I have a formula in one column [Invoice Year] that returns 1,2,3,4,5 based on what year of the project the invoice falls in The formula is…
-
create a filter/checkbox to identify rows created after 12:00Pm
I would like to create a filter/checkbox to get the rows created after 12:00PM to 5:00 PM same day. Is there a way we can do this?
-
Average calculation from overall ratings
Hi All, I'm trying to calculate average of these rating in Average column excluding "NA", for example if "NA" is applied in any column the formula should exclude that column and calculate for the remaining columns. Please help me with the formula.
-
IF / AND formula help
Hello, I would like to add a condition to an IF formula that changes the health colour only when the Due date is not blank. Please can you assist? At the moment the formula looks like this which works fine for tracking due dates: =IF(Status@row = "Complete", "Green", IF([Due Date]@row = TODAY(), "Blue", IF([Due Date]@row <…
-
Formula Expert Help Needed (Number of days for Oldest Ticket, Count all except blank & Closed status
Hi Smartsheet Formula experts! I spend a lot of time scrubbing the community board for formula help and thankfully I usually find my solution. (Thank you all!) However, I'm trying to build this sheet summary out and after about 2 + hours, I'm reaching out to the experts in the community. Screen shot of the metrics I am…
-
Current week & previous week
I have a sheet with start date and finish date columns and a column to insert current/previous or blank (if the dates are neither current or previous weeks), so was thinking the formula would be something like if today's date is greater than or equal to the start date or less than or equal to the finish date insert…