-
Calculate Days Open?
Hi everyone, I am struggling with figuring out what formula to use to calculate days open between two date columns on a Sheet. Column 4 = Submitted At Column 5 = Closed Date Column 6 = Days Open No matter what formula I try to use (NetworkDay/NetworkDays/Sub/Days, etc), I get the #invalid Data Type error. Any ideas why?…
-
duplicate entry checking 2 sheets
I have an invoice intake that pulls into an 'In Process Invoices' sheet. Once the invoice gets to a certain approval point it moves to another sheet 'Completed invoices' I need to ensure that there is a notification/color change/something that notes that an invoice number is a duplicate. i.e. once the new invoice is…
-
Formula to pull latest updates from a row
I am trying to setup a dashboard that gets updated with KPI's monthly. But, I only want the most recent entry to be reflected in the charts/dashboard. I get the gist of how to do it with if/or/isblank formulas, but can't quite get it right. It's reversed right now, I can fix that. Am I on the right track here, just need to…
-
How to use a nested if with count
We would like to have all the criteria for a created date by week. the number of new helpdesk tickets created by each end user for a given week. Example: Week of 7/7/25 Mickey submitted 5 tickets, Donald Duck submitted 10 tickets, part of Donalds 10 tickets only 5 were reported in week 7/7/25. Report Week of 7/7: Name:…
-
Rank Formula Multiple Results
I have previously received help with Ranking formula where there is more than 1 result it needs to rank the most recent week, which was resolved and worked fine - until last week. https://community.smartsheet.com/discussion/105462/duplicate-rank-what-formula-would-return-the-most-recent-week The new issue is that I have…
-
Using If And with symbols
Hello, I'm currently trying to write a formula and am running into issues. I've added a "Project Health" column with red, yellow and green symbols. I would like these to change automatically using the following cells to determine. If "Arrival Date" is blank, and "Customer Requested Delivery Date" is within 2 weeks, red If…
-
How do I only get the 25, 26, 27, etc out of 2025, 2026, 2027?
I need to pull out the last 3rd and 4th characters of a long year format CCYY where I only pull out the YY. So, in my example, I need to find the year 25 out of 2025. 26 out of the year 2026, 27 out of the year 2027 and so on. Basically ignore the first two characters of the long year 2025 so I will only be seeing the 25…
-
VLOOKUP/INDEX/MATCH Function When Using Automation To Input Date/Time
Hello all. I currently have two sheets. The main one is where people make requests, and I need to calculate when a person 1. Starts the request, 2. When it's on the next stage, 3. When it is completed. So I set an automation to copy the row onto the second sheet when the status is changed to "Start", "Next Stage",…
-
Calculating Time Duration
Hello everyone - I would appreciate some assistance with calculating time. I have been through the 19 page discussion on time and have tried multiple formula combinations, but cannot seem to resolve the specific issue I am having. Basically, I need to be able to automatically calculate the hours and minutes between various…
-
Maintain delimiters in a join() column when exporting to excel.
Hello, we have a bunch of columns with multiple selection dropdowns. When I export to excel the selections are concatenated without spaces: I tried to create a helper column using JOIN(): which mostly works, although it only ever shows a space between the text, and not the delimiter. However, on export to excel the helper…