-
Returning date either 15th or 31st of the month
Do we have a formula for rounding off dates to either 15 th of the month or 31st of the month? for example if the date falls between 1-15 of a month, I need 15th as the date for that month and year. and if a date falls in between 16-31 then it should return 31st of the month and year. Kindly advise. thanks for your help.
-
Military Time Calculation: Night Shift
I have columns set up in 4-digit military time. The formula below calculates day shift (0800 to 1700), but returns a negative value when calculating night shift (2200 to 0600). How can this be resolved? =((VALUE(LEFT([End Time 1]@row, 2)) + VALUE(RIGHT([End Time 1]@row, 2)) / 60) - (VALUE(LEFT([Start Time 1]@row, 2)) +…
-
COUNTIF Formula Help
I'm not sure if I'm making this more complicated than I need to... I'm trying to build a COUNTIF formula that accounts for 2 unique values in User Satisfaction column and can have anywhere from 4 -17 unique values in Team Name. =COUNTIFS({User Satisfaction}, "4. Very Satisfied", {Month Survey Sent}, [Column3]@row, {Year…
-
Multiple If statements
I am struggling with a multiple if statement....I need to have 8 total. Below is just a start with two. They each work individually (in separate cells) but when I put them together I receive the UNPARSEABLE error. Anyone that can help me get this to work? =IF(OR([Place in Funnel]2 = "Universe", [Place in Funnel]2 =…
-
Basic Formula
How can I write a working formula to count number of all cells in a column (excluding blanks) of a different sheet that don't equal a certain date? Example: COUNTIF(Referencedsheet_ColumnName(all but blanks), <>"12/20/1900") Thank you Sorry it is too basic but I am a beginner.
-
Smartsheet Error even though sheet is functioning at speed with no other issues
Myself, and many of my users are encountering this message on a sheet that I have. It doesn't effect performance, and I have zero connectivity/access issues, but I get this every time I save. Is anyone else having this issue? It only seems to occur on the most complicated of my sheets. @Genevieve P @Paul Newcome
-
Send daily alert to those who have not submitted
I need to monitor daily, those who have not submitted their temperature and type of their status daily before 10am of the day and inform them to submit their status through a form link. How can I automate the daily alert to those who have not submitted their temperature for the day. I used automate "Alert someone" base on…
-
Multi-select string operation
Hi all, I have three columns which have the same drop down options: requested, received and pending. I would like the "pending" column to have all options from the "requested" column except for what's in the "received" column. For example: under "requested": option 1,2,3,4; under "received": option 1,2; under "pending"…
-
How to get smallest, 2nd smallest value and 3rd smallest value
Hello, I need to run a formula which return smallest, 2nd and 3rd smallest value for a criteria. Example like below, when I key in "Banana" in cell D1, the result in F2 to F4 will give me 100, 150 and 200. This formula does not work because Smartsheet do not support array formula. Anyone have alternative way to make it…
-
Match email from one sheet to another
I would like to set up a formula that searches an 'email' column from one sheet compared to the @row email in my main sheet, and if that email is in the other sheet colum, returns a yes or no so I can trigger off of that. I've looked at VLOOKUP and INDEX/MATCH, but I can't seem to find a similar use case... Sheet 1 = New…