-
Adding Date Criteria to formula
Good day- I have a formula that I need to add an "end date". I am not sure that between dates are available? Here is the formula: =COUNTIFS({Gate1Start}, >=DATE(2020, 1, 1), {PPV}, 1, {ItemStatus}, OR(@cell = "Open", @cell = "Closed")) The date portion needs to be BETWEEN 1/1/2020 and 12/31/2020. Right now it is just…
-
How can I connect project names to a project #?
I am looking for a better way besides individually making automations. That allows me to connect multiple project names using a drop-down list to a specific project number.
-
Formula for countdown symbols
I'm wanting to use the Empty Quarter...Full symbols to act as a clock for when a contract is about to end, but I'm having difficulty with the formula. Hoping someone can help. I have 4 columns: Execution/Begin date, Contract End Date, Total Contract Time, and Days Until End Date. Days until End Date is using the formula…
-
Best practice / ideas for dealing with recurring task reminders
Hi, I'm creating a sheet which will contain a list of about 200 tasks. These tasks are all recurring, although not with the same frequencies. Some need to take place annually, some are quarterly, some monthly (and not always on the same day of the month). I need to create some way to track whether tasks have been completed…
-
Combining Multiple IF AND Statements
Hello, I am trying to combine the 3 statements into one. They all work independently however, I am missing something when I try to combine them. These each check a checkbox if the statement is true. Help Please! =IF(AND([Trans date falls w/in Black-out Period]@row = 1, [Covered Person?]@row = "Yes"), 1, 0) =IF(AND([Covered…
-
COUNTIFS and Duplicate Values
Hello! I need to figure out a way count the number of records in my issue log, EXCEPT if the records have the same parent number, they can only be counted once. Is there an easy way to do this? Is there a way to use the DISTINCT formula in the "Parent" column, but also count the blank rows, as long as the Record column…
-
Find middle number using LEFT and RIGHT Formulas
I have a column with dates that look like this 2022-10-18. I am trying to create a formula using LEFT and RIGHT to only give me the month (in this example = 10) Please help, thanks!
-
Formula question for Month Data
I need some help figuring out a formula. Wanting to sum up the amount of trainings each person enrolled in the month of April. Can't seem to figure it out. Here is what I am trying: =SUMIF([Trainings/KPL]:[Trainings/KPL], [Assigned OBC]:[Assigned OBC], "Gladys Risling", ([Start Date]:[Start Date], MONTH(@cell)4)
-
Formula help
From my sheet I need to populate a checkbox based on these criteria VDate is less than WDate AND Status = Live This formula is working to accomplish the first criteria but I can't get the status part to work. =IF([VDate]@row < [WDate]@row, 1) Thanks for the help, I am horrible at formulas!
-
Average of column if dates meet certain criteria
I'm looking for help on a formula to calculate an average of a column if the date in another column falls between the nominated start and end date. I tried a few different formulas which came up with #UNPARSEABLE, and this one: AVERAGEIF({Date Completed}, AND(>=[Start Date]@row, <=[End Date]@row, {Number})) resulted in…
