-
TODAY() returns yesterday or tomorrow depending on your timezone
Hi Community, We have an interesting issue here in Melbourne, Australia when we use the TODAY() function. At various times of the day, it can return yesterday's date rather than today's date (as it should). Took quite a while for this to be acknowledged, but the behaviour has been confirmed now by a couple of senior…
-
Average Collect formula
I'm trying to collect the average survey response number for a quarter for the current year and multiply it by 0.2, for a percentage, but it's coming back unparseable. The first referenced column is a 1-5 number, the months are from a numerical month helper column, 'Created' is the auto-create date. Any ideas?…
-
Changing Status Dot Color Based On Deadline Column
Hi, I've tried looking up other questions to answer this and got as far as this formula [=IF(Deadline@row <= TODAY(+14), "Green", IF(Deadline@row <= TODAY(+7), "Yellow", IF(Deadline@row >= TODAY(0), "Red", "")))], but it's not providing the proper results. I have a Deadline column and a Status column (which is the RGY…
-
Need help with Formula
Here is my formula to track Corrective Action status based on total attendance points. =IF([Total attendance points]@row > 7.9, "TERM", IF([Total attendance points]@row > 5.9, "FINAL", IF([Total attendance points]@row > 3.4, "WRITTEN", IF([Total attendance points]@row > 1.9, "VERBAL", IF([Total attendance points]@row <…
-
Help with Index/Match & Vlookup
I need to create a parent row that shows a rollup view of budget for all of the children, but I don't want to add another row/line item to my Master Sheet with the parent row because it will get too complicated. Instead, I'd like to create a separate sheet with the Unique ID tied to each project category, and create an…
-
Sumifs multiple criteria single column help
Hello, I'm having trouble creating a formula that will sum if multiple criteria in a single column are met. I want to sum LOE column when the capability = one of 4 values. I'm using the SUMIFS and OR functions to try to make this work in a sheet summary metric but getting the #UNPARSEABLE error. Found a few other posts on…
-
Convert Time/Date to Date Then Convert to Future Date
Data that I'm importing via Data Shuttle includes a date field (named Date Issued) with this format: 2022-01-22T10:30:55.999999889172613350 I added another date field (named Issued Date) where I added this formula to pull out the date only: =IF([Date Issued]@row <> "", MID([Date Issued]@row, 6, 2) + "/" + MID([Date…
-
COUNTIFS always returns 0
I can't get my COUNTIFS equation to work, it always just returns 0, can anybody help? =COUNTIFS([Column A]@row, "None", [Column B]@row, "None", [Column C]@row, "None", [Column D]@row, "None", [Column E]@row, "None", [Column F]@row, "None")
-
Ranking/VLookup Formula with Duplicates
I have an issue with my sheet where we rank the total number of projects closed from 1-5. Rank is in the first column, second column is a formula: =MAX(COLLECT([Total Project Closed]:[Total Project Closed], [Rank for Completion]:[Rank for Completion], [Rank for Sheds Comp]@row)) this returns the number of projects closed.…
-
Need Help with UNPARSABLE ERROR and BLOCKED Formula error
I need a Date (the Actual End Date) in the Description Column Row 35.when the Task cell equals the task of current row. and if no date in that cell to be blank Current formula is =IFERROR(DATEONLY(INDEX(COLLECT([Actual End Date]59:[Actual End Date]354, [Summary Formula Helper]59:[Summary Formula Helper]354, 1,…