-
Getting #Invalid Operation
I have the following formula and am getting an #Invalid Operation message. I only get the error though when it returns "Red". The column is defined as a Date and is restricted to dates only. I think it has to do with the TODAY() part of the function. =IF(AND([Due Date]@row < TODAY(), [Due Date]@row > " "), "Red", "Green")…
-
Report does not display sheet dates or dropdown menu items
Hi there, I used the "Project Management Office" template to set up different projects and then a report that pulls the information from all the projects into a central report document. I have noted that if you have drop-down options in your original sheets, or dates, these are not displaying in the report. How do I…
-
Return different value based on date
Hi, I'm trying to write a formula to return different values based on the reference date ([Next Calibration/PM Date]@row) compared to today's date. I want the equation to: If the cell is blank, return as "Pending", If the date is < = today, return as "Overdue", If the date is > today, return as "Current", If the date is…
-
"Record A Date" automation not working
I'm trying to set up a multitude of automations where, when the status of an item is clicked as "complete" it will auto-populate the date into a date column. I set up an automation for this function but when I go back into the sheet to test the function, it's not working. I previously set up a conditional format that…
-
Record a date of last attached file
Hi all, I have a main sheet that gets updated every time we manually attach a new file to it. I have a number of reports and dashboards that automatically update upon this manual attachment. I would like to record the date upon which the main sheet was updated specifically via attachment of this file (we update the sheet…
-
Count based on Multiple Criteria & Dates
Hi, I am attempting to create a formula that would = Count the number of training items completed per team member and between two dates. I created this formula, it is giving me all Zeros: =COUNTIFS([Actual End Date]:[Actual End Date], <=DATE(2024, 1, 1), [Actual End Date]:[Actual End Date], >=DATE(2024, 12, 30),…
-
If formula with Date and Time
Hi. I created the formula below to close a dynamic view on Oct.30th. =IF(TODAY() <= DATE(2024, 10, 30), "Open View", "Lock View") How do I adjust that formula so that the view can be locked at 5pm on that day?
-
Automation-Date in the past
I have a column named "Shipping Date" and then, a second column with a formula that gives us a date 5 weeks prior to that "Shipping Date". This second column triggers and automation to contact the customer once the date is reached. What if the line is entered and the 5-week gap is a date in the past? How should our…
-
Help with COUNTIFS
Hi Community! I am hoping someone can help me figure out how to correct this formula or create a new one. I want to count how often the regional VP’s name appears in a row with a date within a specific date range (quarter). Current formula I am using in a calculation sheet and referencing another: =COUNTIFS({TLH User…
-
Counting Days with plus or minus options
I know, I know… use =Networkdays([start date]1, [end date]1) HOWever, I am trying to track the performance of when a job is completed according to its due date. I found adding a -1 to to stop the inclusiveness of the calculation, whereas I can get a job completed on 10/5 that was due 10/6 to show 1 day using…