-
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…
-
Counting # rows submitted in a particular month and year
Hello, my current formula only counts what month a row was submitted but not the year. How can I get the formula to also count the row if it happened in January of the current year? =COUNTIF({Date Submitted}, IFERROR(MONTH(@cell), 0) = 1)
-
SUMIFS Help
Hi all, Having an issue with a formula; I want to sum column 3 based on fulfilling criteria from 2 other columns eg If Column A says "Product" and column B says "2024 May" then sum column Column C It may seem simple, but having issues... Thanks
-
Remove Accented characters and replace with non-accented characters
Hello, I have an intake of name data in three columns: First Name, Middle Name and Last Name. I have then got a formula to join them up in a fourth columns: E.g., Selma Anna Żakowicz will become: Żakowicz/Selma Anna Joined Up Formula: =[Last Name]@row + "/" + [First Name]@row + " " + [Middle Name]@row This joined up column…
