-
How to check a box if a date is in the past 7 days & next 14
Hello, I want to check a box if the following criteria are met. I want to then base an automation to send out rows that are not complete and were due in the last 7 days / due in the next 14. Criteria: Status is not Complete Finish is in the past 7 days (overdue) Finish is in the next 14 (look forward 2 weeks) This is the…
-
I am looking for a formula to pull specifics from a range of data (see screen shot attached)
The goal would be to pull in "For the role of Market Lead, here is the list of systems that role will need access to: (Example: Director of Operations will need FIS Client Portal and Black Diamond). From there, I will index/match the correct system owner and set up a workflow to request that they provide the needed system…
-
Count values in a column based on values in a different column
I am trying to count all cells that are "green" that are also a "task". "Green" is in a column called "Health" and "task" is in a column called "task type". =COUNTIF({Turnaround Plan Range 2}="Task" If(AND({Turnaround Plan Range 3}= "Green")
-
Creating a status using project stage
My data is a list of jobs all having 8 applicant stages. These 8 stages will ultimately represent 6 statuses. I am looking for a formula that will let me assign a status to each job based on the 8 applicant stages. Because jobs can have new applicants and an applicant in the offer step, I need it to work from the furthest…
-
RYG Formula for Date Started
Hey all. Very new to any type of formula composition. This is likely very basic for some. We have a "Date Started" column and "Date Completed" Column. Essentially I would like to set up a Red, Yellow, Green Status column tracking each project. My guidelines would be "Green" if date completed has a date. "Yellow" If between…
-
COUNTIFS on checkbox columns with multiple criteria
Is there a way to have a countifs formula on multiple columns? I want to count the cells where PM=Alex and there's a checkbox in the last 4 columns. =COUNTIFS(PM:PM, "Alex", [Agenda 6/7/21]:[Agenda 6/7/21], 1, [Min 6/7/21]:[Min 6/7/21], 1, [Scorecard 6/7/21]:[Scorecard 6/7/21], 1, [Updates 6/7/21]:[Updates 6/7/21], 1)
-
Options where more than one 'Modified By' column is required
Hello I have a sheet where I need to capture the name of the person who 'checks a box'. I have used the 'Modified By' system column to do this. Unfortunately, I have 3 check boxes in total. Any ideas on a workaround, other than a copy row to two sheets with an INDEX MATCH pulling in the name on the 'master' sheet? Or a…
-
Calculating days in a month from a date
I need to calculate the days in a month from a date. I have 2 columns: Check-In Date Check-Out Date What I want to do is the following: Create 2 columns: Days in Current Month Days in Next Month On the 2nd entry, I want to count the days in July. So the returned value would be 1. Ignore the 3rd entry since all of the days…
-
How do I summarize data by a particular condition and by Date/Year?
Hello, I am attempting to create a dashboard that will summarize the cost of overtime for employees working at various locations on a month by month basis to compare cost between either/both conditions (by location and/or by month). In order to do this I have parsed out that I will have to summarize the data in a separate…
-
Help with YEAR TODAY()) in SUMIFS Formula
Hi everyone. I am hoping you can assist me. I am trying to SUMIF the number of assignments we received in the current year, by month and by the service team. My formula is: =SUMIFS({WORKSHEET COLUMN WITH RANGE}, {WORKSHEET COLUMN WITH RANGE}, =[NAME OF REGION]@row, {COLUMN WITH MONTHS DISPLAYING AS JAN, FEB, MAR, ETC},…
