-
COUNTIFS with @cell refrence
I have a column in one sheet that has data in it such as 0423-01,0423-02,.... the 04 meaning April and the 23 meaning 2023. The -01 or -02 is just the numbering system for that specific month. After April is over there will be more rows added below the 04 ones but now starting with 05 to denote we are now in May. I am…
-
Converting seconds to dd:hh:mm:ss
I am trying to import a report from Service Now that shows how long it took for a ticket to close. The duration field from Service now to excel export is in seconds. I need a field that would show the duration in days, hours, minutes. Service now has a formula for excel but I am having trouble converting it into…
-
fill values in a formula in one cell with value from another cell
I have a bunch of formulas that pull figures from another sheet based on a start and stop date. They work just great. I am trying to keep from having to change the date over and over in all of the formulas for each week of each month by using "Helper" Cells. You put the desired date range in for each week one time and the…
-
How can I work around the Formula Cell Reference Limit?
Hi, I'm getting the below error message. My needs are too complicated to convert any of the formulas to automations and I'm not sure how to simplify. Here is essentially what I am trying to do - every row is a different item and columns A, B, C, and D contain fabric codes. In columns A1 and A2 (and B1, B2, C1, etc.) I need…
-
AVG COLLECT for End Date with current YEAR
I am getting an #UNPARSEABLE error with this Sheet Summary formula: =AVG(COLLECT(Duration:Duration,Level, 0, Status:Status, "Complete",Status:Status, "In Progress", [End Date]:[End Date], IFERROR(YEAR(@cell),0)=YEAR(TODAY()))) I would like to return the average Duration where: row Level is 0, Status is Complete or In…
-
Return Date that falls within 10 days of another date
So essentailly we have 2 sheets. On 1 sheet we have a list of test dates(call it test dates). Then on the main sheet we a column for due dates for certain documents where if not turned in Student would be locked out and can not take tests. So we want to have another column on the main sheet that pulls dates from the test…
-
Need Summary Sheet formula to count age in weeks of assignment
I have a column which is the Assigned Date of a task. I use this formula in the summary sheet to count how many items are a week old from todays date: =COUNTIF([Assigned Date]:[Assigned Date], What would the formulas be to count assignments that are 2 weeks old, 3 weeks old, 4 weeks old, etc.
-
Hours tracking in schedule.
I need some help troubleshooting a formula. I have viewed the numerous posts on time tracking in smartsheet and have used the formulas in this post: https://community.smartsheet.com/discussion/67111/calculating-military-time-from-am-pm-format. My problem is the when any time past midnight is still showing up as "PM"…
-
Insert row between WBS and append to WBS numbering convention
Is it possible to insert a row between a WBS structure and rather than the added row taking on the next available number I would like to append and additional decimal to the WBS so the order of tasks is not confusing. For example in the screenshot below the WBS is working great, however when I want to insert a row between…
-
Formula to collect nonblank values in assigned to column for sheet summary
Hello, I'm hoping for some help or prior experience with a layered formula I am trying to create. I would like to pull all of the names assigned to tasks into a sheet summary field where the value is not blank. Ideally the formula would only pull unique values. I was trying to work with combinations of the INDEX, COLLECT,…