-
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…
-
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) +…
-
Nested Formula for At Risk Flag
I've been trying to create a nested IF/AND function. I can get them to work individually, but the formulas are unparseable when I try to nest them. Below is an image of my sandbox where I am learning formulas. Here is what I am attempting to flag in the RISK column: If FINAL DUE is today or in past, AND If FINAL DUE is not…
-
Time calculation formula using an IF variable
I am trying to write a formula that counts the number of days we left to finish specific work cases. The end result that I'm trying to calculate is the number of days remaining to work a second level case based on two variables: 1.) how much time is allowed for a specific case type, and 2.) how much time was exhausted…
-
Suggestion for returning data based on selected criteria
I'm looking for some guidance. I would like a cell to return text (category) based on selected criteria (sub category) -- screen shots below. Example: If I select 'Wine' from the drop down, I would like the Category cell to return 'Food & Bev'. I could do an IF statement, however, there are several subcategories in the…
-
Having trouble with the move function
I've added a column with a simple formula to check a box when a column clears itself of an error =IF(ISERROR([Written number Ref]7), 0, 1) - woks fine no problem, the box gets checked when it is supposed to Once the box is checked I want to use the move function to move the row to a different sheet, however no luck there -…
-
[help] Using SUMIF formula to calculate the sum of a column if between certain dates
Hey y'all! Here's what I have so far: =SUMIF({GRAD Student - Support Request Range Complete Date}, AND(<=DATE(2019, 5, 9), >=DATE(2019, 1, 7)), {GRAD Student - Support Request Time Spent Range}) Complete Date = the date range I'm trying to pull from After the "And"= The range I want for the dates. Time Spent = The location…
-
Using Modified Date in a formula returns #date Expected
Hello, I am trying to determine whether or not a row should be added to a weekly report by trying use the modified system column to calculate the associated Friday. e.g. It's Tuesday and I make a change, I want my other column 'Report week end date' to display that upcoming Friday. =IF(WEEKDAY(Modified@row) < 6,…
-
IF Statement-If text is updated in my "Comments" within the last 5 days I want to return Yellow Sta
Looking for formula help. I have a comments column that I want to make sure is never more than 5 days old. If the comments field is updated with new or different information - I want to return a Yellow star to the Updated field or a date for which it was updated on. Is this possible? Thanks Molly Morton