-
Nested IF function
Hi I am currently trying to create an IF function that will mark a row as overdue if in the 'status' cell is "Paid" or "paid through urgent payment". At the moment i have it working for just one criteria, =IF(AND([Days Until Delivery]@row < 0, NOT(Status@row = "PAID")), "OVERDUE", "") where do i add the second criteria…
-
Can you write an IF/THEN statement to return a date when the checkbox is clicked.
I have a column which assigns a specific department a task when the button is checked. I would like to populate the assigned date based on this check. So when I assign to purchasing by hitting the check mark, the assigned date column is automatically filled with todays date. I'm not the greatest at if/then so any help…
-
Reference cells in another sheet WITHOUT having to use named ranges
Hi - can anyone tell me how to use the following formula on a *different* sheet than the columns referred to in the formula... =SUMIF(MonthYear:MonthYear, FIND("19", @cell) > 0, [AW1]:[AW1]) (This formula works great when it's in the same sheet (A) as the one with columns name MonthYear and AW1, but I can't figure out how…
-
Formula Help??
I'm trying to set up a formula to calculate a Reminder status by days. I have a deadline date that is in the past, but it does not turn my status column to a "Red" dot, it remains green and I do not know how to add to this formula to make that happen. Here's the formula I have in place, and it works, but I need status to…
-
Calculate average durations between dates
I have an original sheet with hundreds of rows of information (Source Sheet) that I am looking to summarize calculations in to a second sheet (Summary Sheet), in order to feed in to a dashboard. Source Sheet has two date fields that I am interested in - lets refer to them as an 'open' and 'close' date. I would like to…
-
How to include double quotes in the output of a text formula
I am trying to combine several text elements into an image attribution for each row on my sheet. Here's my formula: ="American Society for Microbiology " + Category1 + ". " + Title1 + ", " + SubmitterFirstName1 + " " + SubmitterLastName1 + IF(ISBLANK([Co-creator #1, Name]1), "", ", " + [Co-creator #1, Name]1) +…
-
How to Enter an estimated date of completion formula
Good Afternoon, I am trying to enter in a estimated date of completion formula into my smartsheet 20 days after the row (Today's Date) has been entered. Can someone please assist me? This is the formula I am using at the moment and I'm unsure of where I am going wrong. =([Today's Date]:[Today's Date])@row + 20
-
Functions and Formulas
Hello, I am trying to write formula to calculate difference in BASELINE END DATE to actual END DATE =[Baseline End]58 - [End Date]58. Now I want to compensate for RAIN DAYS but receive UNPARSEABLE =[Baseline End]58 - [End Date]58+(duration)4 Where I missing it? Thanks, Gary
-
I need help bucketing some data
I need help combining some data I have the following columns in sheet-1 · Year-1 Deliverable Date-1 (this is a date column) · Year-1 Deliverable-1 Amount (this is a currency column) · Year-1 Deliverable-1 Billing Quarter (this converts the deliverable date into Q1-Q4) I need to create a formula in sheet-2 that will bucket…
-
Stopping %Complete if end date is past but Task is incomplete
Good morning, After a long search I am finally reaching out. In my % Complete column I am using an IF(ISBLANK w/ Networkdays formula to get my percent complete number; Pulling dates from 'Proj. Task Launch' & 'Proj. Task Comp.' I can't seem to find the right formula to stop the '% Complete' (lets say at 75%) if the 'Proj.…
