-
Exclude and If statement then "" and based on date then "Return Value"
=IF([Tier Status]@row <> "Group D", IF([Escalation Required]@row, <>"", (IF([WM Next Meeting Date]@row = "", "No Meeting Scheduled", IF([WM Next Meeting Date]@row = TODAY(-14), "Pending", IF([WM Next Meeting Date]@row > TODAY(-1), "Scheduled", IF([WM Next Meeting Date]@row < TODAY(-14), "Overdue"))))),"Escalated")) What…
-
I want my Status column to give me a red circle when it detects a flag in my Critical column.
=IF([% Complete]@row = 1, "Blue", IF([% Complete]@row > 0, "Green", "Yellow")) I want to add there to turn red when it detects a flag in my Critical column. Please, help
-
Need to return a value with INDEX(COLLECT) but not if cell is BLANK
Hello, I am unsure if what I am doing is possible or not but I need to return a value with specific criteria. I need the latest "Revised Date" from the most recent amended version (Occurrence). However, it is possible the latest amended has no updated "Revised Date" field so it should keep the previous revised date.. In my…
-
Formulas to calculate the number of weekdays, Saturdays, and Sundays between two dates
I have three columns: Weekdays, Saturdays, and Sundays. I need to calculate the quantity of each of these three types of days based on start and end dates. The NETWEEKDAY and NETWEEKDAYS functions do not work in this case because the start and end dates should be included in the count. Example: Start date of 8/22/2021 and…
-
Forms - can you make formulas on dates?
can the sheet have a formula when the form selecting a date - gives a response? =IFS([@Response]="", " ", OR(WEEKDAY([@Response])={1,7}), "That is a weekend date. Please select a different date.", OR(WEEKDAY([@Response])={2,3,4,5,6}), TEXT([@Response], "dddd")) where the form entry will prompt a new suggestion?
-
Link to another sheet and conditionally output
Hello, I have one sheet that I am trying to link to and output data conditionally. There is a "LineItem to WPID" sheet where I am trying to bring in the WPIDs from my "WPID Matrix" where the LineItem header matches with the LineItem record if the Department is "CF". Is there a function I can use to bring in this data? See…
-
Time calculations with data collected by a form
I am using a form to collect data that includes a unique ID for each user. The user will check in by completing the form with their ID and "Arriving". When the user is done, the user completes the form again and selects "Leaving". I now have two rows of data where I extract the time in and time out from the creation…
-
INDEX(COLLECT) function problem?
I am using an INDEX(COLLECT) function to return an amended date by family name. It works well except in the event a family was amended twice. It is returning the first amended value as opposed to the latest amended value. How can I get Smith to show up as Aug-27-2021 in Column 5? When I change row index to 2 it works for…
-
Health and Status roll up from task to child to parent
My roll-up formula in my parent row is working perfectly and captures the information from its primary children rows. =IF(COUNTIFS(CHILDREN(), "Red") > 0, "Red", IF(COUNTIFS(CHILDREN(), "Yellow") > 0, "Yellow", IF(COUNTIFS(CHILDREN(), "Green") = COUNT(CHILDREN()), "Green", IF(COUNTIFS(CHILDREN(), "Blue") =…
-
Formula for checkbox if meets criteria
Hi, I've kind of asked this question before, but the formula that was suggested doesn't work on multiple dates. I have sheet A and Sheet B Sheet A has columns of ID# and Date Sheet B has columns of ID#, Date 1, Date 2, Date 3 etc... The formula I have in sheet B didn't work when there is a different date column in the same…