-
Checkbox Formula: If Due Date has passed but completion date is blank
I have two columns: 1: A "Date Calc" field (contains a formula) that automatically displays a due date for a task based on manually entered dates from a prior field. This is a date column. 2: A Date Complete field that is manually entered. This is a date column. I have added a third column that will automatically check a…
-
Sum of children if it has no descendants
I am attempting to create a single cell formula to capture the total value within a budget sheet, however =SUM(DESCENDANTS()) is capturing data multiple times and doubling costs due to children rows being routinely being summed (=SUM(CHILDREN)) by staff for reconciliation purposes. The sheet layout has a parent row with…
-
Index Collect Max | Get the most recent item based on Created Date
Im using the formula below, but getting an error #INCORRECT ARGUMENT SET =INDEX(COLLECT([Current Note]1:[Current Note]7, Auditor1:Auditor7, Auditor@row, [Created Date]1:[Created Date]7, MAX([Created Date]1:[Created Date]7)))
-
Need to Figure out # of days each emplyee is on leave in a given month
Hi Everyone, I'm working on a work days calculation for a given month, for that I need to calculate the leave days for a given month. Below is my grid. Orage colors columns is in different sheet - Leave requests are updated here and green color columns is in different sheet - Need to figure out total # of days on leave in…
-
Pre-Approved Changes Pulled From Approved Standard Changes
Hello! I'm attempting to create a change management system wherein the pre-approved changes can reference previously submitted (and approved by our CAB) standard changes. Pre-Approved Changes - need it to be a drop-down list of pre-populated requests that are Standard change requests that have been approved to become…
-
Workaround to show date for grouped items
Is there a workaround to show the date in the report? I want to show the data in one line but I don't want to group data and collapse it, I want to show Name, Total price, and created date as one line item if the name matches
-
SUMIF Different Columns
Hi, I have two columns [Initial Priced Amount] and [Final Account Amount], what I am trying to do is use the amount in [Initial Priced Amount] if the [Final Account Amount] is blank, but if it isn't blank then use the amount in it, the [Final Account Amount] column. I want to use it in a couple of formulas in other columns…
-
NETWORKDAYS/IF/ISBLANK - Can you subtract from the returned day count number?
Hello! I am having a hard time figuring out a formula that will count the NETWORKDAYS between a [Start Date]51 and the end date [Escalation Completed Date]29 that will count as TODAY() if the end date cell [Escalation Completed Date]29 is blank AND if the [Days On-Hold]29 is NOT blank subtract the number in that cell from…
-
Creating a formula to determine cost based on different factors
Hello! I am trying to find a formula. Basically, in column A I have a number (example-1500) and I am looking to find a formula that will calculate the cost based on range. I.E. if the number in Column A is 0-1500...multiple by 5. If the number in Column A is 1501-2000, multiple by 10. Can anyone guide me in the right…
-
Formula for changing symbol colors
I created a column that changes symbol colors depending on the start date column. The below formula I put together after reading through some other posts within this community works alright except for the bit to have the symbol change to gray when the start date is today or past. What am I missing? =IF([Start Date…