-
How can I calculate days between dates if a field is blank? (Days open counter)
My column property set to "text/number". The formula I am trying to use: =IF([Completion Date]@row = " ", "Closed", TODAY() - [Creation Date]@row) I have a column named "Creation Date", then I have a column named "Completion Date". When there is a date in the completion date column I want it to populate "Closed". When the…
-
AVG, COLLECT, and LARGE cross-sheet formula
I would like to pull the average test score based on multiple criteria, but instead of a specified date range I would like to use the top 10 most recent entries as not all candidates have the same number of tests taken within the same date range. I started with the following date range formula: =AVG(COLLECT({score},…
-
Function for Counting Children of Parent Rows
Hi - I have been wrestling with this function and need some help. Here is the data: In my sheet summary, I am trying to enter a function to count the children who answer yes. I have tried it a bunch of ways, and it will count if I only ask it to count children, or if I only ask it to count the yes answers, but I cannot get…
-
Populate shift pattern on date rows
Hi all. We have a sheet with column named “Employee” and “Shift” followed by a string of columns with dates to make a schedule. Each cell will either be blank if the EE is not working or will contain a number that reflects where they will be working. I’m trying to find a way to pre populate the cells in a repeating pattern…
-
Any Idea why a formula would change ?
This If error statement was manually typed in but the sheet. It never said FM pilot2. The sheet changed the text without me knowing. What would cause this?
-
Index/Collect #INCORREECT ARGUMENT SET
We have a formula error I just can't seem to correct. I want to return an abbreviation in column "CaseType" based on the value in column "Petition Type" I am using the following formula: =INDEX(COLLECT({Petition/Case Type Matrix Range 5}, [Petition Type]@row, {Petition/Case Type Matrix Range 2}, 1,)) References:…
-
Weeknumber transition from end of year to new year
I currently utilize a formula to calculate items due this week, next week, week 3, etc. Because of the transition to the new year, the formula I use is no longer counting from January 1, 2022 & beyond. Can someone please assist me with a formula update that will (hopefully) provide a seamless EOY transition, moving…
-
Getting #UNPARSEABLE error
Hi all, I've four colums A, B, C, D. Objective is to A*B + C*D = total number. I'm getting an error. Not sure why? Highly appreciate if anyone can help. Attached is the error! Thanks.
-
How to extract Date from String "Sunday, December 12 4:25 PM"
Need to transform the month and date into date format from "Sunday, December 12 4:25 PM"
-
Formula question
I have one drop down column "Turbine Operational" with the options Yes or No. Another column "MW Impacted" is a text column. I'd like to be able to input numbers into the MW Impacted Column, but if the Turbine Operational column has "Yes" selected, then that overrides my inputs and puts the cell at 0. Any insights…