-
Return task name associated with a future date
I have a project with linked tasks. I can get the start date of the next task using the following formula: =MIN(COLLECT(Start2:Start6, Start2:Start6, >TODAY())) How can I return the task name associated with this upcoming task? In this example, this formula gives the next start date as 6/19/19. What formula will return…
-
Formula for Actual duration excluding duplication
Hi Community, I am trying to calculate the actual duration starting from a list of Start and End dates. What I am trying to achieve is to exclude duration overlaps in order to get the actual effort days only, but including not contiguous time ranges. Please see example: Start: 01/10/2019 End: 04/10/2019 = 4 working days…
-
Date Comparison in Functions is Not Working
The following formula returns a value of zero (0): =COUNTIFS({DATE ONLY}, =DATE@row, {Shift}, ="1st", {Degreaser Check Passed?}, <>"BLANK") Here is the sheet that is being referenced: DATE : Contains only dates or blank cells. (formula driven) {DATE ONLY} : References a column that contains only dates or blank cells.…
-
Return status of cell (blank vs. full) at today's date.
Hello! I am trying to use a function that will tell me whether a cell is vacant or full at today's date. We are trying to create a desk booking system for our office, so the end-goal would be to have a vacant/occupied indicator on a SS dashboard. My test sheet is below: So, to determine whether Desk 1 is occupied or…
-
How to calculate tenure (years of service) between 2 dates?
Hello, I am trying to get a date on the Years of service column. In excel you just minus today's date-start date and it will give you a result with smart sheets I can't figure out how to do it. Can somebody help me?
-
General Update on Assignments formula with Today - x
Hi all, as you may be all aware, I'm definitely not the most versed in the formulas when it comes to Smartsheets. I'm a self-study without any prior experience and I did learn a lot and can address the majority of issues myself, being thankful to you all. Now, however, I am asking for help again. I'd like to build a…
-
Created Date Formula for Forms
Is there a formula that will capture the created date for new entries on a form? Once the form is completed, I would like a column to automatically populate the day that row was added from the form so I can track response times. Is there anything like that available other than the audit log?
-
How can I rollup a sum using an id in two separate sheets?
I have one sheet, the Purchasing Request sheet, which tracks a single request made. The ID column serves as a primary key here. On the other sheet, I have items that are tracked and related to a given request. The ID column in this sheet serves as a foreign key, which ties back to the one request made. I would like to try…
-
Need help to adjust automatically my Order Date using Date Formula
Hi, I have two date columns in my sheet 1st column is "First Order Date" and the next one is the "Next Order Date" with underlying formula of =[First Order Date]@row+ 30, we need to supply our clients every 30 days. The problem here is that using that formula "=[First Order Date]@row+ 30" will give you all the day in a…
-
Formula to reference two values to return a third?
Hello everyone, Hoping someone can help. I am wanting to create a formula that, based on existing criteria in two cells, will dynamically return the value in a third cell. From my "Aggregator/Calculator" grid (screenshot below), from the "Health" column at the far right, I need to return six symbol indicators each to their…