-
netdays if formula returning #invalidoperation
Amateur formula user here! I've tried rearranging this formula few different ways, and I believe my issue is just the order of events? I'm looking to calculate length of stay by using =netdays with the "Enrollment Date" as the start and the "Discharge Date" as the end. However, if discharge date is blank, then the end date…
-
Is there a formula to specify the status of parent row based on status of the children?
I have a project with several milestones that I've designated as children of my main project. These milestones in turn have their own children (tasks). I would like to use a formula that would specify the parent's status based on the status of it's descendants. In particular, I would like the status to be: "Not Applicable"…
-
Formula to auto-populate data base on date range
With reference to the above, we are trying to do the following: The "Date columns" (1/1/22, 2/1/22...) to capture the "10. Unit" when it matches the Start and End date. The scenario is that we have 20 beds in the dormitory and there will be different crew who will need to stay in on different time. We have to ensure it…
-
Sum Values from a Reference sheet with multiple criteria to populate a master sheet
I don't give up easy but I am throwing the white flag. I have a master sheet for project records and summarized data. Most of the time is was a one to one reference - Easy Peasy. Not anymore, I now have a 1 to many situation. When a project is created a budget is approved, however we are now allowing the ability to request…
-
Project Health Formula #2
Team, I think I accepted the answer too quickly to my question Project Health Formula answered by Kelly 12.1.2021. Previous to the formula below first child row below was complete and second child row was cancelled (blank) making the parent row '"green". I thought the parent row should have read 'blue' Now, when I change…
-
Automated Move Rows won't move anything
I have an automation set to run whenever a "Status" row changed (or row is added) with a condition: "Where Status Contains 'Out of Service'". It's set to alert me, then move rows. It alerts me just fine, but won't move any rows. Using the row options to move the rows works with ease, and no error messages ever appear. My…
-
Formula - Index/Collect/Match by rank
OK, I'm stumped! Here is my source sheet And here is my destination sheet What I want is... Column: Contractor A Return Value From: Source Sheet Company Column Where: Source Sheet, Type Column Matches Destination Sheet Category Coulmn Source Sheet Rating Coulmn is the highest value. Columns: Contractor B & Contractor C…
-
Net duration between 2 dates AND hours
Hi, I use this formula to calculate duration in minutes between two dates and time (see picture attached). Goal is to time delay of response between reception of email and answering email but to exclude hours people are not working. Here is the formula to Start time : =IF(SUM(VALUE(LEFT(RIGHT([Date et heure du courriel du…
-
Average If
=AVERAGEIF({Project Master Log - COPPERA Range 7}, {Project Master Log - COPPERA Range 6}, "Pending", {Project Master Log - COPPERA Range 2}, Week@row, {Project Master Log - COPPERA Range 4}, Year@row) I'm trying to average the gpm% of the items that are pending and match the date ranges.
-
Collect the most recent Data from 2 sheets
Hi All, Here is the scenario, I have a master list of clients with the pertinent data for our business partners to reference. How ever we have an update sheet that has been built to request changes to that master list if needed. I would like to have data update in my row of the master list once there is an update from the…