-
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…
-
Updating "master" task sheet status from six sub-project task sheets
I want to keep task sheets for six projects. I want them to feed to a "master" sheet. I have successfully created the automations to copy tasks as added to the six sheets to the master. Where I am struggling is in updating the master as tasks move from "new" to "in process" to "complete". I don't want to use an automation…
-
Conditional logic in a form
I have created a form with conditional logic. The logic displays "catalog responsible" (column was previously set as contact list) and it is displayed only if the previous field is selected with "prod" option (column is set as dropdown) The form works perfectly, but since other group of persons will have access to the data…
-
SumIf
I am trying to use the SUMIF Formula to reference a sheet to add hours based on the name of an employee. I have a Names Column with a list of names and an Hours Column. On a second sheet I am trying to print out their hours added together based on their name. My attempt =SUMIF ({HoursCol}, Name@row = {NameColum},…