-
Formula for calculation
Hi Guys, I'm stuck with one critical problem, where I need one formula. I have two sheets Sheet A (contains only applied leaves, not all the dates range) Column 1 - Leave Start Date, eg 01 jan 24 Column 2 - Leave End date, eg 03 jan 24 Column 3 - Employee code, eg ABC001 Sheet B (contains entire range of dates for…
-
Formula for 24hour clock overlap - booking clashes at the same time on the same day
Hello all, I am hoping someone out there has a smart idea of how to pick up on reservations overlapping within a 24hour time period. In this instance, there are two people parking in same bay on the same day. Formula I have is: =COUNTIFS([Date Of Parking]:[Date Of Parking], [Date Of Parking]@row, [Bay Number]:[Bay Number],…
-
Help with formula
Hello I am working on this new formula where are trying to calculate the verification due date. The original way we were getting the value was when the date format was in a MM/YYYY but now the dates will come in a MM/DD/YYYY format when having this format the output of the formula we created for the Verification Due Date…
-
Perpetual Contract Renewal Formula
Hello All! I am trying to create a formula that will automatically update the end date to a contract based on a renewal timeframe. For example, Contract Start date 12.31.19, Contract end date 12.31.20 at the end of the contract, the contract will renew for 1 year perpetually on the same date. Right now we are at 11.8.24,…
-
Avg Collect Formula Error
I am attempting to create a formula for a dashboard that pulls data from a sheet and needs to reference the amount of days from Approved to contacted for a specific Agent and by Month. I tried to use several formulas starting with the following: This pulls back all 0's. I also tried this formula and got the error #Divide…
-
Automate Weekly Copy/Paste of Hierarchy
I have a weekly tracking sheet that I update each week by doing the following: 1) Copy/Paste the current week's hierarchy (lines 546-561 in screenshot) to the last row of the sheet, 2) Update the week column, and 3) Clear some of the data for the new week. While this doesn't take a long time for one sheet, I do a similar…
-
Free Smartsheet Webinar Series by DigitalRadius
I wanted to share an exciting resource that I think a lot of you will find valuable! Our Customer Success team here at DigitalRadius is hosting a Free Smartsheet Webinar Series now through the end of the year, designed to help you unlock the full potential of the Smartsheet platform. It starts tomorrow! Here’s what’s…
-
Duplicate Formula
I have a Smartsheet which is fed from a form an Intake team is using to gather prospect information and we are having issues with duplicate entries. The sheet/form has a field for prospect first name and another for prospect last name which we would like to check for duplicate entries of. I created a column combining both…
-
COUNTIF
I am trying to COUNTIF range 1 is yes then range 2 meets any of 3 criteria. A single criteria works =COUNTIFS([Current Status]:[Current Status], "Active", [Milestone/Deliverable Name]:[Milestone/Deliverable Name], "Parent Project") I need to add two additional criteria for the same second range. I want it to also count if…
-
Create Column Formula on Duration column
For a duration column being calculated as the difference between two date/time fields, the Create Column Formula option was unavailable. (Converting the column to a text/number column allowed application of the column formula, which then persisted when converting the column back to a Duration column, so the underlying…
