-
Return Text that is in a Data/Number Column that captures dates
In the table below, I have 3 columns that are text/number fields: Final Document, Target Date, Actual Date. In the Target MM/YYYY and the Actual MM/YYYY, I have a formula to pull the MM/YYYY from the Target date and Actual Date, but as you can see, there is "On Hold" and "Cancelled" text instead of a date in some of the…
-
Drop Down List not populating (Mobile Version)
Hello i have a quick question and was wondering if anyone was experiencing similar issues and have found a work around. I put together a checklist for our MHE and would like to post a QR code to the Smartsheet around our facility. The issue i am experiencing is one of my columns with a drop down box is not populating on a…
-
countifs and a date that falls between two dates
Please help! I have two formulas that I am struggling with. I am trying to count how many tasks have been completed/closed within the past fortnight. I came up with the following formula but it gives me the 'invalid operation' error. What am I doing wrong? Formula=COUNTIFS({LV_ BLD CONST QUERY REGISTER _status}, "closed",…
-
Need someone's logical brain on a formula
Hi all, I need help fixing this formula: =COUNTIFS({TGN Bluesheet Tracking List Range 4}, =DATE(2023, 6, 20), {TGN Content Range 2}, =AND("Facebook" "Instagram" "Threads" "X" "YouTube")). I'm trying to pull the number of a specific dropdown list from a specific date. Any ideas on the best formula?
-
Extract text from a file string
Hello! Simple question (I hope). I have a list of document locations - that look like this: _Central Documents/Training/Investigators/Rhodes, Meredith/CV I would like to parse 'Investigators' into a staff role column, 'Rhodes, Meredith' into a contact column, and 'CV' into a document type column. What is the basic formula…
-
Using the holiday parameter in workday function
=IF(Runs@row = "Daily", WORKDAY([Completed Date]@row, 1, {Holidays Range 1}), IF(Runs@row = "weekly", WORKDAY([Completed Date]@row - (WEEKDAY([Completed Date]@row) - 2), 5, {Holidays Range 1}), IF(Runs@row = "Monthly", WORKDAY(DATE(YEAR([Completed Date]@row), MONTH([Completed Date]@row) + 1, 1) - 1, 1, {Holidays Range…
-
Date formula
Hello! I currently have 2 columns on my sheet, one where I enter date processed and date expired which has a formula to calculate 1 year expiration from date processed. I have been asked that no matter what date I process, it needs to expire the last date of that month. So for example, date processed = 9/5/23 date expired…
-
Convert to Column Formula Error
I'm trying to make the below formula a column formula. If I copy and paste the formula into cells, it works as expected. =IF(OR([Status]1 = "Complete", [Status]1 = ""), "", "Yes") Two issues, though: 1) The [Status]1 brackets are being automatically removed and reformatted as Status1 2) When I try to convert to a column…
-
Count & Sum Distinct Values based on Criteria | COUNT(DISTINCT(COLLECT(...) | Returning 1 ???
In a ROLLUP sheet, referencing a source sheet, I'm trying to count (and sum) DISTINCT values that meet multiple criteria. My formula keeps returning "1", when in the shown scenario below has "80" distinct values. Hoping I'm just missing something simple. Rollup sheet purpose = count the distinct values rented within…
-
I am identifying the duplicates in the sheet. What wrong with this query?
=RANKEQ([Request ID]@row, COLLECT([Request ID]:[Request ID], [Item Number (SUPC)]:[Item Number (SUPC)], [Item Number (SUPC)]@row, Duplicates:Duplicates, 1), 1)
