Formulas

Hi all, I need to add a formula to a sheet that will create 120 rows for the same date for all days in 2024. For example, 1/1/24 will have 120 rows, then 1/2/24 will have 120 rows and so on. Is this possible? Thanks! Jackie

I've built a sheet that calculates the days since last event. I display this on a dashboard in chart form. However, I don't want the days to just keep increasing indefinitely. Is there a formula or a way to "cap" the days, for example ">365".

Hello, I'm looking to have a formula to display the next/upcoming date in the list. The formula is on another sheet (Sheet = "Helper Calculations"), so I need to reference another sheet (the one in the table below - Sheet = "Holiday Dates") within the formula. On this sheet, I can confirm the Date column is a Date type. I…

Hi :) I am using this formula to calculate the days between when an action was Requested and when it was Completed, with an extra bit on the end to add the word "days" to the count result. =[Completed]@row - [Requested]@row + " days" My issue is; when the Completed cell has no date I still get the word "days" when ideally…

I have child rows that use a formula to calculate % completion based on input to another column. I'd like the parent row to return a value based on the child values (Specifically, sum of the child rows divided by the number of child rows). As the child rows use a formula rather than a direct number entry, entering a…

This seems like such an elementary question but I keep going around and around on this and I can't find what I'm doing wrong. I'm trying to count the number of parent rows in the sheet (each parent row represents an "Employee Name"); I want to include this count in the sheet summary but I can't seem to figure it out. This…

I have been looking at the various conversations on using a COUNTIFS with an embedded CONTAINS function, but have had no success getting it to work. In the below formula I want to count "FAA Domestic" and recommendations that aren't closed (0) if they contain an assigned office that contains AIR-62. The below formula…

Hello, First time asking a question. Forgive brevity/waffle! I have been struggling to understand the syntax issue I have when trying to generate a T-shirt size based off the 'Duration [Days]' column I have. If 'Duration [Days]' is known/calculated from START/END Dates can I generate the t-shirt size automatically? My…

Hi all, I have a box with a dropdown menu with 3 options but you can put multiple items into the box. The dropdowns are GPA PACE Max Time Frame I have 3 columns check "gpa" check "pace" check "Time Frame This is a sample formula there is one for GPA and PACE =IF(CONTAINS([SAP Issue]@row, "Time Frame"), 0, 1) I want the…

This might be my first real contribution to this community, but I think I have had a breakthrough with respect to background auto refreshing a sheet. I've seen the common solution of utilizing a workflow to trigger a daily update of a date column, but the challenge has always been that you are obviously limited in how…