-
Converting Dates to Month and Year
I am trying to use this formula to get my dates to just show the month and year. However, all of my January dates are missing the year. Why is January missing the year? =IF(MONTH([Start Date]@row = 1, "January", IF(MONTH([Start Date]@row = 2, "February", IF(MONTH([Start Date]@row = 3, "March", IF(MONTH([Start Date]@row =…
-
Calculating % with a minimum value
I'm looking for a formula that calculates the percentage of two columns, but shows a minimum percentage of 50% if the calculation is less than that. Is this possible to do in one column, or do I need to have one column with the percentage, and the next with an IF function? My knowledge of functions is limited so thanks in…
-
VLOOKUP Reference Vacation Calendar to Update Approver via Automated Workflow
Hi, I have an employee Vacation Planner sheet with a status column/formula that indicates whether an individual is currently on "PTO" based on today's date and whether it falls in the range of their vacation's start and end date. In a separate Smartsheet, we will be managing service tickets and certain types of requests…
-
COUNTIFS multiple columns
Hi @Paul Newcome I have another interesting scenario I wonder if I can solve with a formula. I have three columns with numbers 1....5 I want to calculate between those three columns anytime a value is >=1 to count as 1 and not every time the value is true. I know I can count the three columns =COUNTIFS([Summer…
-
Formulas and Links to Other Sheets Disappeared
I have a large project sheet that contains (1) cross-sheet links and (2) formulas to calculate due dates based on other tasks. I needed to change the sheet to only populate weekdays (no weekends) so I turned on the dependencies and edited the workdays. Then I realized ALL of my cross-sheet links and formulas disappeared! I…
-
How to calculate total hours of work per month for each resource
Hi all, l am trying to sum the total number of hours each person within our team has worked across different contracts. However l am not sure the correct formula to use for this? I believe if would be a SUMIF but every formula l attempt either brings it back as invalid or unparseable. I have attached a screenshot the…
-
Status RGYB balls formula, when due tomorrow and past due
I built out this formula to change the status marker balls colors based on today's date and my "due Date" column: =IF(Status@row <> "Complete", IF([Completion/ Posting Date]@row > TODAY(10), "Blue", IF([Completion/ Posting Date]@row > TODAY(5), "Green", IF([Completion/ Posting Date]@row > TODAY(2), "Yellow",…
-
Column Formula to mimic HLOOKUP?
Hi, Gurus! I have a sheet that calculates 2 values based on whether the calculated columns contain an "X" in Row 2: #Actual formula is: =SUMIF([Week 1]$2:[Week 300]$2, "X", [Week 1]@row:[Week 300]@row) [has "X"] #Forecast formula is: =SUMIF($[Week 1]$2:$[Week 300]$2, "", $[Week 1]@row:$[Week 300]@row) [no "X"] I need to…
-
Adding Matching Barcodes/SKU's from a transfer sheet onto a master Inventory sheet
Hello all, I'm new to smartsheets and working on setting up a barcode based inventory management system for the warehouse I run. I feel like I have the transfer sheet up and running just need to get all of that information to condense onto my sheet. Would someone be able to point me in the right direction or find what I'm…
-
SUMIFS #INCORRECT ARGUMENT SET
Can some please explain the reason for the error? I don't understand what is wrong with what I have typed. =SUMIFS({STD HOURS}, {STATUS}, ="*** COMPLETE ***", {COMPLETION DATE}, AND(IFERROR(WEEKNUMBER(@cell), 0) = [Week #]@row, IFERROR(YEAR(@cell), 0) = {YEAR SELECTED})) {STD HOURS} is a column with a formula that returns…