Date + days remaining = auto generate estimated completion date

Hi! I have a sheet where I'm making projected completion dates. I know that for the total work remaining we have 88 days. Is there a formula to add today's date (dynamic) + 88 days (dynamic)?


Thanks!

Best Answers

Answers

  • this makes sense and is now performing exactly as I'd like. Thank you for your help.

  • Jeana
    Jeana ✭✭✭✭✭✭

    @Genevieve P I have a related question if you don't mind. I'm working on a formula that will return the week (Mon - Fri dates) based on the date in another column (END DATE).

    So if the END DATE is 10/21/2020 I would like to return the Mon-Fri dates for that week - 10/19/20 - 10/23/20.

    Based on this thread I can easily determine the the date 14 days in advance, 21 days in advance, etc. I'm stumped on the formula to actually return the Mon-Fri dates for the week that the END date falls in, if that makes sense.

    I'm already calculating how many weeks in advance the date is but I'd like to display on a Dashboard the dates of that week. Any help you could offer would be greatly appreciated.

    Thanks,

    Jeana

  • Hi @Jeana

    No problem, I'm happy to work this out with you! Now, is this date in the same sheet as the dates you want returned? I'll build this out presuming that they are.

    So you essentially need 7 results, or 7 formulas, one for each day of the week, correct?

    In that case, in the SUNDAY date cell, you'll want to use a Nested IF statement with the WEEKDAY function to say, if this date is Sunday, return that date. But if it's Monday, return that date minus 1 (which would be Sunday). If it's Tuesday, return that date minus 2, etc.

    Here's an example if the date you're looking for in the END DATE column is in the same row as your Sunday column:

    =IF(WEEKDAY([End Date]@row) = 1, [End Date]@row + 2, IF(WEEKDAY([End Date]@row) = 2, [End Date]@row + 1, IF(WEEKDAY([End Date]@row) = 3, [End Date]@row, IF(WEEKDAY([End Date]@row) = 4, [End Date]@row - 1, IF(WEEKDAY([End Date]@row) = 5, [End Date]@row - 2, IF(WEEKDAY([End Date]@row) = 6, [End Date]@row - 3, IF(WEEKDAY([End Date]@row) = 7, [End Date]@row - 4)))))))


    Otherwise, if the date is only listed once in your sheet, you'll need to use an absolute row reference. Then for your MONDAY date, you'll need to adjust how many days you're taking off or adding on to the End Date, based on when it falls during the week.

    For example, here's Tuesday, referencing the End Date in row 1:

    =IF(WEEKDAY([End Date]$1) = 1, [End Date]$1 + 2, IF(WEEKDAY([End Date]$1) = 2, [End Date]$1 + 1, IF(WEEKDAY([End Date]$1) = 3, [End Date]$1, IF(WEEKDAY([End Date]$1) = 4, [End Date]$1 - 1, IF(WEEKDAY([End Date]$1) = 5, [End Date]$1 - 2, IF(WEEKDAY([End Date]$1) = 6, [End Date]$1 - 3, IF(WEEKDAY([End Date]$1) = 7, [End Date]$1 - 4)))))))

    Look how I adjusted the returned value to say =IF(WEEKDAY([End Date]$1) = 1, [End Date]$1 + 2

    This means, if the date is 1, or Sunday, and I want to return TUESDAY of that week, well then I will want the End Date + 2.

    Let me know if I've understood what you're looking to do, and if this will work for you! If not, it may be helpful to know a bit more about where this End Date is stored and where you want your formula output to be (screen captures would be ideal, but please block out any sensitive data).

    Cheers,

    Genevieve

    Join us at Smartsheet ENGAGE 2024 🎉
    October 8 - 10, Seattle, WA | Register now

  • Hi @Jeana

    I've just re-read your post and I realize I may have misunderstood. Did you want the text "10/19/20 - 10/23/20" or the text of the Monday date - then - Friday date for that week to appear in a cell in the same row as the End Date? With many possible End Dates in that column?

    If so, we would use the same formulas as discussed above, just add the one for Monday and the one for Sunday together with a = " - " between them.

    This will look long & complicated but it's just two long formulas added together. Since I used @row I converted this to a Column Formula as well.

    Here's the sheet:

    Here's the formula. I've bolded the + signs so you can see where I've added two together:

    =IF(WEEKDAY([End Date]@row) = 1, [End Date]@row + 1, IF(WEEKDAY([End Date]@row) = 2, [End Date]@row, IF(WEEKDAY([End Date]@row) = 3, [End Date]@row - 1, IF(WEEKDAY([End Date]@row) = 4, [End Date]@row - 2, IF(WEEKDAY([End Date]@row) = 5, [End Date]@row - 3, IF(WEEKDAY([End Date]@row) = 6, [End Date]@row - 4, IF(WEEKDAY([End Date]@row) = 7, [End Date]@row - 5))))))) + " - " + IF(WEEKDAY([End Date]@row) = 1, [End Date]@row + 5, IF(WEEKDAY([End Date]@row) = 2, [End Date]@row + 4, IF(WEEKDAY([End Date]@row) = 3, [End Date]@row + 3, IF(WEEKDAY([End Date]@row) = 4, [End Date]@row + 2, IF(WEEKDAY([End Date]@row) = 5, [End Date]@row + 1, IF(WEEKDAY([End Date]@row) = 6, [End Date]@row, IF(WEEKDAY([End Date]@row) = 7, [End Date]@row - 1)))))))


    Let me know if this works!

    Genevieve

    Join us at Smartsheet ENGAGE 2024 🎉
    October 8 - 10, Seattle, WA | Register now

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!