-
Question on Value function
Hello. Using this formula, =IFERROR(VALUE(LEFT([Time to Close (Bus Hours)]@row, 3)), IFERROR(VALUE(LEFT([Time to Close (Bus Hours)]@row, 2)),VALUE(LEFT([Time to Close (Bus Hours)@row, 1)))) I'd like to be able to take a string that contains hours and minutes, separated by a decimal point - for example 259.04 would give me…
-
Formula to count days past due but leave the column blank if the task is completed
Hello, On my sheet I have tasks that have a column for Due Date, Completion Date, and Checkbox for Done. I want to keep track of the tasks that are overdue and have an easy way to glance and see how many days they are over due from the due date. I have been successful in using this formula for that calculation =TODAY() -…
-
Identify deliverable date at the parent level
I'm using these formulas to show the next milestone date: Milestone checkbox column: =IF([Expected Finish*]@row = MIN(COLLECT([Expected Finish*]:[Expected Finish*], [Expected Finish*]:[Expected Finish*], >=TODAY())), 1, 0) Next Milestone Date: =MIN(COLLECT([Expected Finish*]:[Expected Finish*], Milestone:Milestone, 1))…
-
SUMIF - Referencing another sheet
I'm trying to sum based on certain Streams(column name) in another sheet 'Register' Stream data is as: Emergency Youth The formula I've used is: =SUMIF({Register Range 3}:{Register Range 3}:, "Emergency") I'm getting an unparseable error Please help!
-
Returning the most recent value that matches multiple criteria (cross-sheet lookup)
Hi All, I am trying to create a preventative maintenance schedule for our fleet vehicles and heavy equipment machines based on the vehicle/machine, the most recent odometer readings or machine hours, and the type of service needed (PM Service 2, PM Service 3, Oil Change, etc.). For my source sheet, I'm using a repair and…
-
Count If from Roll Up
Hi all, I am trying to count the number of occurrences of certain keywords from a sheet column into a rollup. I cannot seem to get the formulas right. Error coming up as #UNPARSEABLE Keyword "HHS", Sheet Name "25", Column Name "Funding Agency" =COUNTIF({25 Range 1}[Funding Agency]:[Funding Agency], "HHS") A little…
-
#INVALID Ref in metrics sheets
Metrics were working fine until today then the above appears in place of all formulas = dashboard not working too
-
How do I make the numeric value returned in this formula usable in other mathematical formulas?
Value returns correctly, but I need to be able to add that value to OTHER formulas and fields. =IF([Target Level:]@row = "Standard", "$19,000", IF([Target Level:]@row = "Superior", "$100,000", IF([Target Level:]@row = "Premier", "$240,000", IF([Target Level:]@row = "Elite", "$490,000", IF([Target Level:]@row = "Platinum",…
-
Edate formula
I want to calculate the contract expiry date based on the current contract term. If the day of start date <=15, the expiry date should be the last day of the previous month if the day of start date is the expiry date should be the last day of the current month any help would be great thank you
-
Sumifs, referencing another sheet with two conditions
I am trying to add numbers based on 2 conditions. I am referencing another sheet. I am using the following formula: =SUMIFS({FY21 AA AMER Budget US$}, {FY21 AA AMER Quarter}, contains "2021",[{FY21 AA AMER Marketing Cost Type}, contains "Content"]) I want to add the budget for AA AMER if the following two conditions are…