-
How to get the average number of days within a quarter
here is my formula, but I keep getting an error. I can get the average, but now I want to know the average within Quarter 1 =AVG(COLLECT({DYS to Completion}, {Qtr}, "1"))
-
=iferror issue
Hi I have a formula as follows in a cell, however when there is no data to display I do not want the message Invalid Data Type formula is =NETDAYS([TESTING1]@row, TODAY()) I have tryed =iferror(NETDAYS([TESTING1]@row, TODAY()) but had no luck Hoping someone can advise the fix and what went wrong please
-
Determine follow-up date that will trigger Workflow
I want a date to populate a field that will trigger a follow-up workflow. My criteria is: If the certification Complete is GREEN, this date can remain blank, as no follow-up will be sent. If the certification Complete is YELLOW or RED, the Manager has either partially completed (yellow) or not completed at all (red). The…
-
Help with formula
Hello I am working on this new formula where are trying to calculate the verification due date. The original way we were getting the value was when the date format was in a MM/YYYY but now the dates will come in a MM/DD/YYYY format when having this format the output of the formula we created for the Verification Due Date…
-
Cross Sheet References
Hello, I have a tasks list sheet with a parent row as the top level PO # and the children are the subtasks and various line items under the PO #. I want to create a sheet that grabs the DRI person for each subtask but only once at the top level PO (the person will always be the same for the various line item #s under one…
-
Formula for calculation
Hi Guys, I'm stuck with one critical problem, where I need one formula. I have two sheets Sheet A (contains only applied leaves, not all the dates range) Column 1 - Leave Start Date, eg 01 jan 24 Column 2 - Leave End date, eg 03 jan 24 Column 3 - Employee code, eg ABC001 Sheet B (contains entire range of dates for…
-
Formula for 24hour clock overlap - booking clashes at the same time on the same day
Hello all, I am hoping someone out there has a smart idea of how to pick up on reservations overlapping within a 24hour time period. In this instance, there are two people parking in same bay on the same day. Formula I have is: =COUNTIFS([Date Of Parking]:[Date Of Parking], [Date Of Parking]@row, [Bay Number]:[Bay Number],…
-
Perpetual Contract Renewal Formula
Hello All! I am trying to create a formula that will automatically update the end date to a contract based on a renewal timeframe. For example, Contract Start date 12.31.19, Contract end date 12.31.20 at the end of the contract, the contract will renew for 1 year perpetually on the same date. Right now we are at 11.8.24,…
-
Avg Collect Formula Error
I am attempting to create a formula for a dashboard that pulls data from a sheet and needs to reference the amount of days from Approved to contacted for a specific Agent and by Month. I tried to use several formulas starting with the following: This pulls back all 0's. I also tried this formula and got the error #Divide…
-
Automate Weekly Copy/Paste of Hierarchy
I have a weekly tracking sheet that I update each week by doing the following: 1) Copy/Paste the current week's hierarchy (lines 546-561 in screenshot) to the last row of the sheet, 2) Update the week column, and 3) Clear some of the data for the new week. While this doesn't take a long time for one sheet, I do a similar…
