-
calculate number of full months between start and end dates
I'm looking for a formula that will calculate the number of full months between 2 dates that will be used for calculating a goal amount by multiplying a monthly amount x the number of full months (partial months are prorated). In my example, I'm using =ROUNDDOWN((([End Date]@row + 1) - [Start Date]@row) / 30.425, 0) It…
-
A True Mind Bender - Tracing Tasks through Predecessors
Is this even possible? I have a task (say Task 18) that is preceded by other tasks, all joined by predecessors. Task 18 is the "final" task. Task #18 is preceded by Task 16, and Task 16 is preceded by Task 14. Outcome - I want to filter rows to see only the tasks that have a relationship to "Task 18." I'm thinking that I…
-
Formula that Recognizes a Cell's formatting
Is there such a thing? If a cell has a fill color of Red, can a formula read that cell and recognize that fill color? Same question for any kind of cell formatting - strikethroughs, font color, bold, italic, etc.? The reason for the ask is that I have a plan that has A LOT of conditional formatting, and I want a report…
-
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…