-
Why does my MID formula work when I subtract the incorrect length?
I made typo in one of my formulas but it still returned the desired result. I want to understand so I don't end up with unintended results down the line. My goal is to extract the data that follows "Set 4:". So in this case the formula should return "0101". It should always return the data that follows "Set 4:". My…
-
How to get individual cells to show remainders.
I'm currently working on making two separate sheets. One is where providers can submit that they have extra of something (someone submits that they have 5 extra bags of concrete, another person submits that they have 7 sheets of metal, etc.) with their email attached (so multiple of the same kind of material cant be…
-
Countifs Formula
Hi Smartsheet Team, I have captured this student id no. 2307647 Programming session hours is 1.5 hours. (see below) Then, I worked on the metrics sheet to show the hours on the programming session - Complete. It shows only 1 hour, even though I added the decimal spaces. the formula for Programming sessions - Complete is…
-
Date/Time formula - Need help
Hi all, I need to add a date and time check to a SmartSheet I am working on. Currently, we are trying to track late arrivals into our building. Example: =IF([Actual Pick Up Time]@row > [Critical Pull Time]@row, "Late", "On-Time" =IF([Scheduled Pickup Date]@row = [Actual Pick Up Date]@row, "On-Time", "Late") The above…
-
Counting Opened Opened for a Particular Year
Hello, I want to count the number of tickets that were opened in 2022. I am getting a invalid date type error message. The date range in the cross reference is restricted to dates only. =COUNTIF({Date Requested}, YEAR(@cell) = 2022)
-
Smartsheet bug? Time / Rounding formula error
Hi, I'm using the following formula to calculate the difference between 2 times: =IFERROR((VALUE(LEFT([End Time]@row, FIND(":", [End Time]@row) - 1)) + VALUE(RIGHT([End Time]@row, 2)) / 60) - (VALUE(LEFT([Start Time]@row, FIND(":", [Start Time]@row) - 1)) + VALUE(RIGHT([Start Time]@row, 2)) / 60), " ") And then using the…
-
Add timestamp to Join/Collect Column Formula
Hi, I have a Join/Collect column formula that I'd like to add a timestamp to. Is this possible? If not, is there a workaround? Sheet1 contains a list of Accounts populated with answers from a questionnaire. An Account can have multi rows. Sheet2 consolidates these answers into a single row. The column formula in Column3 is…
-
adding a decimal to a date does not round up
I have a field that is calculating the average of days, which comes in as a decimal. When I go to add this decimal number to a date to get a future date, it only uses the whole number of the decimal, and if the decimal number is 15.94, it only uses the 15. Why would it not use 16?
-
Checkboxes and populating data
Hi all, I have 3 different sheets set up. Sheet 1 a list of names with assorted data for each name. There's a check box that will move rows to sheet 2 if checked. Sheet 2 contains data that was moved from sheet 1. There's a second check box column on both of these sheets, let's call it "active" In Sheet 3, I have the same…
-
How to prevent Countifs column formula from updating previous cells.
I have created a log book request form that needs to generate a unique ID (YYYY-##-###) based on 3 criteria: Year submitted (4 digit year), Site Code (preassigned 2 digit site code), and Log Book Type request count. The formula I'm using performs as needed, except it changes previous Log book numbers that have rows with…