-
Determine if Created Date is after 12:00 PM
I'm calculating turn-around time for requests. I'm looking for something like the following. =IF([Created Date] >12:00 PM, DATEONLY([Created Date]@row)+1, DATEONLY([Created Date]@row)) So if a request was submitted 1/19/23 after 12:00 PM I want to add 1 business day to the date. After that I'll calculate my turn-around…
-
Current Week for Period
I'm trying to use a formula to identify the "Current Week" in my file. The file calculates the reporting period based on the date the new row is created. It also calculates the reporting week number based on the period ending date. The issue is that when I use the WEEKNUMB function to calculate the "Current Week" it does…
-
Basic NETDAYS Help!
Disclaimer: I'm a SmartSheet beginner, and only at an amateur level with Excel formulas. I've reviewed quite a few NETDAYS posts on here already and tried variations of the solutions in all, but so far every NETDAYS variation I've tried has returned the #UNPARSEABLE error. I'm trying to write a formula that will calculate…
-
INDEX(MATCH) or JOIN(COLLECT)?
When we first started using Smartsheet, the rep who handled our "Launch Package" created the following formula. =IFERROR(IFERROR(INDEX({SupplierItemNumber-AL}, MATCH($[Our Part Number]@row, {OurPN-AL}, 0)), INDEX({SupplierItemNumber-MZ}, MATCH($[Our Part Number]@row, {OurPN-MZ}, 0))), "ERROR - CHECK PART#") It searches two…
-
Formula for return X value if there is ANY text in a cell, otherwise return text in another cell
Hi! I am working on a formula for a sheet that has a product column, a Use Cases column and a Tool column. I'm hoping to use a formula that says, "Look in the Use Cases column for any text. If you find text, return a "Use Case" in the Product column. If you can't find any words, then return the value in the Tool column in…
-
SUMIFS with Data in another sheet
Hello - I need help with the following. I created a summary sheet with goals by Functional Area (Department). I want to add 2023 actuals (to compare the actuals with the 2023 targets). The actuals information is stored in another sheet. I am thinking about using SUMIFS. Here are my 3 pieces of information: The range to sum…
-
Calculate Total % Complete Checked Boxes of all Parent and Child Tasks
Project: I have a massive checklist we use for event planning (700+ rows!) and want to be able to know how far along in the planning we are percentage wise for one particular event. Goal: I want the Parent row %s to be the total percentage complete of all their Children tasks. The parent row is setup as an event planning…
-
Excluding and Including data in a countifs formula
I need the total number of that meets the following criteria - Banner Name, Open, and not MA or NY. I am able to write the countifs to include the specific banner name and whether the status of that site is open, but I can't figure out how to exclude certain states or include certain states. This is what I tried below…
-
Percentage of "Yes" Values in a Month
I have a sheet that includes a column (named "PTP") of Yes/No data. I need a sheet summary formula that will calculate the percentage of "Yes" answers in that column, across rows that fall within a calendar month, per a date in a different column (named "Actual Start"). Said another way, I need this formula to search…
-
Parent Child Numbering
With A LOT of help from the community I was able put this together but, I think there must be a better way. The last column is what I am trying to get to. Auto-generated Sequential number in this format T-00001 when Ancestors = 0 Auto-Generated Sequential number in this format T-00001.1, T-00001.2 (where T-00001 is the…