-
Extract Number from Cell that Begins with Text
I need to extract all of the numbers from a column and move them to a column on their own. These cells begin with text. The data is not always the same length. This is a sample of the data. Can you help me with a formula that will extract the varying accounts below 45811050-45811050-77510, 45811050-4581-13120.009,…
-
Networkdays when doing sums
I have a working formula (below) that works well, but includes weekends in the return. I am finding a date in a range, and then adding Duration per products X #products. How do I get it to work with weekdays only? =MAX(COLLECT(Assigned:Assigned, Priority:Priority, "HP 1")) + (MIN(COLLECT([product counts]:[product counts],…
-
Using SUMIFS with Cross Sheet References and CONTAINS
There seems to be lots of information on how to use SUMIFS with a CONTAINS criteria, referring to another sheet. Here is my formula on Sheet 1: =SUMIFS({Total Cost - Final Event Space Cost}, {Requested Event Date}, {Requested Event Date} > TODAY(), {Requested Event Date}, {Requested Event Date} >= TODAY(-365)) Total Cost -…
-
Index/Match formula using a range of values?
Hello, I'd like to create a formula or series of formulas to accomplish the following… There are two sheets: Source Sheet Return Sheet Source Sheet: Return Sheet: What I'd like to happen is… on the Return Sheet: When a value is entered in the Constituent Number column, the sheet returns a corresponding value in…
-
Formula to populate price based off 3 criteria
I am new to Smartsheet and am moving a large complex excel spreadsheet into Smartsheet. With the formulas being quite different from excel, I am converting them with some success. But this more complicated one has me confused. I created an example to show what it is I need. I am new to this community, so if I am not…
-
VLOOKUP vs CONTAINS
This discussion was created from comments split from: How to detect multiple dropdown values in a contains function?.
-
Extract a MM/DD/YY Date from M/D/YYYY HH:MM
I'm looking for some formula help to convert a date format. The date column I'm referencing is coming in from another source, and is formatted as M/D/YYYY HH:MM - here's some example data from the sheet: I need to compare these dates to a user-set date (column = Prior Run Date) in my sheet, which is a Date column type…
-
Copy formula to the entire column
Is there an easy way to apply a formula to an entire column? I am using this formula to calculate days between two assignments columns: =NETWORKDAYS([Review Start Date]@row , [Review End Date]@row ) I am calculating the days on the next column. [Days Open] I can click and drag the formula to the other cells in the column…
-
Formula to Calculate Time Taken Between Two Date and Time Columns Stored as Text
Hi Community, I’m trying to calculate the time taken between when something was ready and when it was picked up. I have four columns: Ready Date (Date column) Ready Time (Text/Number, format like 4:05 PM) Picked Date (Date column) Picked Time (Text/Number, format like 4:05 PM) The challenge: Since the time columns are…
-
I have a formula that works on one cell but I cannot apply it as a column formula. Need help.
=IF(HAS([Product Training Type Requested]@row , "Tourlock- 1 Day"), 1, 0) + IF(HAS([Product Training Type Requested]@row , "Turnlock- 0.5 Day"), 0.5, 0) + IF(HAS([Product Training Type Requested]@row , "Circlelock- 1 Day"), 1, 0) + IF(HAS([Product Training Type Requested]@row , "Lifeline- 0.5 Day"), 0.5, 0)