-
Formulas for a range of dates in relation to today.
I am trying to rollup data to summarize how many projects have a completion date in the next 30, 60, and 90 days. Currently, I have them set up like this. =COUNTIFS({2021 Strategic Plan Initiatives Master Range 2}, <=TODAY(30)) =COUNTIFS({2021 Strategic Plan Initiatives Master Range 2}, <TODAY(60)) =COUNTIFS({2021…
-
Pull data based on recent created date
Hi all, I built a sheet that has a simple form for everyone on my team to provide a project update bi-weekly. My superiors are asking for a dashboard that only displays the latest updates. There are 3 columns I would like to reference: Auto-create date to pull the latest update Topic column to distinguish the different…
-
Countif help where range column contains column formula and text
I'm trying to create a summary metric of my active projects using countif. My project status column contains the following column formula, which works fine. =IF([Current Stage]@row = "Stage 5", "Active", IF([Current Stage]@row = "Stage 4", "Active", IF([Current Stage]@row = "Stage 3", "Active", IF([Current Stage]@row =…
-
How do I calculate blank cells by month
I have been tasked to calculate certain segment fields into a bar chart. One of the fields require me to calculate the total number of blank fields for each month. This would require me to compare the date file (which has date in it) to the date processed column (some which are blank). For example, if the date file field…
-
Sheet Summary - Concatenate two formulas
I have a sheet with over 40 areas, each having a separate 'Summary' based on the two formulas. Number of Audits =SUMIFS(Pass:Pass, [Area #]:[Area #], "Area 01") Percentage =SUMIFS(Pass:Pass, [Area #]:[Area #], "Area 01") / SUMIFS(Audits:Audits, [Area #]:[Area #], "Area 01") Is there a way to combine these two, so that the…
-
IF Formula Cross Reference
I am trying to write a formula that references another sheet and returns a "Yes" or "No" to the column I am putting the formula into. I am trying: =IF({CBCT Implementation Schedule 2021 Range 1} = "Completed", "Yes") Essentially, if the column on the reference sheet equals "Completed", the formula would return a "Yes"…
-
copy rows automation with date formula
I have two sheets - one with routine tasks, the other with a list of assigned tasks. I have an automation built into the first sheet to copy rows to the second sheet on a regular basis. The first sheet has a date column which uses the TODAY() function. I'm having a problem with items appearing on the second sheet with the…
-
Auto-Fill Date when Update Request is fulfilled?
Is there a way to automatically insert a timestamp (only need date for my purposes) when an update request is fulfilled? I'm currently manually inputting this data (none, as I only just needed to capture this data), and wanted to know if this is a feature that I glossed over somehow. Thanks in advance!
-
Formula help
I have some columns with formulas that pull in information (if information is available). If the information is not available (filled in), the column puts "#Invalid Value" in that column. Is there a helper row formula I could use to say if the previous column has "#Invalid Value" then enter a space (which I can do) but I…
-
is it Possible to build a Date using Strings(Text Columns)
For example, let's say I have a text Column Called "Day" and another one called "Month" I was trying to accomplish something like this: =DATE(YEAR(TODAY()), MONTH(DATE(YEAR(TODAY()), Day@row + Month@row)), DAY(DATE(YEAR(TODAY()), Day@row + Month@row))) Context: * I'm trying to create a Birthday Reminder, so I want to be…
