-
Counting the date range inside/between two columns?
I'm creating a sheet with one project on each row. Projects have start dates and end dates etc. As the same persons will be working on several similar projects simultaneously, I'd need to be able to calculate the date range (number of days) from the earliest date to the latest date to be able to determine the total number…
-
Average % Complete calculation
HI all, I need advice on the % average calculation at parent level.I’m finding a discrepancy in the average % indicated at parent level. Here is the case, I’m finding a discrepancy in the average % indicated at parent level. Here in the column % Complete, the Row 877-Test0 is having two indents Test 1 with 50% and Test 2…
-
Baseline Start & Finish "Parent Dates" not reflecting correct first / Last children date
Hello @Paul Newcome, I've seen your reply in one inquiry related to my inquiry, but his is about dates being assigned to children tasks. I am using one of the available templates as below. I noticed that the parent baseline dates do not capture the correct earliest date as parent baseline Start date, and the latest end…
-
Can you create a column formula for auto-number generation that will recognize ancestor relationship
I have a sheet that I would like to custom create an auto-number formula so a record ID is given when a new record is submitted, but I also also want it to be able to recognize descendant rows and give them the same Record ID as the ancestor row (instead of generating a new record ID for every new row). Below is an example…
-
How do I count by year based off a condition?
Hello, I'm trying to count how many dates in each state per year (snapshot below). I've tried this formula but it's not counting correctly: =COUNTIFS({State/country}, @cell = "NSW", {Date 6th Day was worked:}, AND(IFERROR(YEAR(@cell), 0) - 2021)) What do I need to change?
-
Parent Row Status Formula Help
In the Project Roll-up Template, I'd like to add a "Not Applicable" as a status selection for rows that won't be applicable. If a single children row is "Not Applicable", I don't want the parent row status to change, if that makes sense. Currently if a single child row is "Not Applicable", the parent row status goes to "In…
-
Health status in a project plan
Hi, I am wondering if anybody can help me with setting up a formula that flags Red, Yellow, Green based on Status and Due Date. If the status is not complete and the due date is in the past more than 5 working dates days, then yellow. If the status is not complete and the due date is in the past more than 10 working days,…
-
Updating/Refreshing my file from a different website?
I have a file that I need to use updated fuel pricing at all times. The website my excel file is linked to updates once a week, usually on Tuesday. Need: How can I have this sheet refreshed from the website (EIA.gov) onto my Smartsheet file? Nice to have: I would like the data point I use on the sheet to be rounded to 2…
-
IF/AND/ISDATE COMBINATION
Hello all, I am trying to say If GPD Release Date UK has a date equal or less than GDP 15 Day Target Date-UK and HA Approval UK has a date, then the result should be "On-Time", if not then "Late". But i keep getting #INCORRECT ARGUMENT SET . What am i doing wrong here? =IF(AND([GPD Release Date-UK]@row = <[GDP 15 Day…
-
How to calculate number of months between 2 dates
Hi, I want to calculate number of months or years between two dates. How to use the below formula which we can use in Excel to calculate that: =DATEDIF(Date column,TODAY(),"M")/12 If the date is 4th Jul 22 then the number of months from today should come to 0.3 and if date is 21 May 2021 then the number of months from…
