-
Flag duplicates with conflicting dates
Hi, I have a list of names with a starting date and a finishing date. I am trying to get a formula that flags conflicting dates for the same person (duplicate). On excel I sorted with a SUMPRODUCT that I know smartsheet does not support. I have also tried to break it down so it adjust to smartsheet but I can't solve the…
-
Formula to identify with min/max date if column is less than 1 and date is less than today?
how do I do this? I have the below which grabs the count correctly for anything less than 100% and <= today's date, but I want to grab the min and max date. =COUNTIFS({Estimating Percentage}, <1, {Est Start Date}, <=TODAY())
-
Why is my formula #UNPARSEABLE
Hoping someone can help... I am trying to get a count of projects opened and closed based on week number and year. Range two below is the year column in another sheet (the column I'm starting with is 2022, but I will be expanding through 2026 once the formula works). Range 1 referenced below is the week number, as…
-
Error calculating time
I believe I have read through every thread on this topic and it has been extremely helpful. However I'm getting a weird error that hopefully someone can help me with. I'm getting a completion time of "10:05 04pm" in row 3, I'm sure this is due to the amount of time for the task - I just don't know how to write this so this…
-
Using COUNTIFS with multiple criteria and OR
Hi, I'm trying to count the number of rows that meet the following criteria (I'm getting an "Incorrect Argument" error. Project Complete is not checked Project Cancelled is not checked Sales & Leads is not checked Month is August Year is 2022 Deployment Type is either New Deployment or Package Upgrade =COUNTIFS({Project…
-
Report to show last data entry only
Good morning, I am looking for a report to return a column value from the last sheet data entry. Essentially, I would like to create a dashboard that tells me whether my machine is online or offline based on the data that the operator puts into the highlighted column. I'm guessing that generating a report would be the best…
-
If function with dates
Hi Experts, I'm trying to create a formula which should give me a Date as result. I have 2 check boxes and depends what it's clicked I would like to get the date below as results. If HALO Calendar is not clicked than DATE DATE(2022 / 8 / 26) IF HALO Calendar and Virtual Model CR1 is clicked than DATE(2022 / 10 / 13) If…
-
IF AND Formula Help
Hi everyone! I am hoping you can please help me. I have tried IF AND, IF OR, and IF CONTAINS formulas but nothing works. Referencing the below screenshot, I have a checkbox in the column titled "Carry Forward". I want to have the check box checked when the column titled "Vacancy Status" contains either the word "Assigned"…
-
Formula for Modified Date
Hello, I want to keep track of End Date changes on workstream plans. Aware the automated Modified Date column is updated following any change to that row. I want to find a way to populate a Changes Log with specific information from a row, if the End Date Column has been updated. Unfortunately, the layout of the activity…
-
Build Report that shows "Child" lines items under the "Parent
I am trying to build a report from a sheet that has a parent line item with child line items. I need the report to show the parent and the child. Then have the ability to group by the parent. When the parent is expanded it shows the children. My report currently looks like this...all the indented child line items are no…
