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 of hours (capacity) againts which I'd then calculate the planned project hours and load.

Could someone more experienced help me on this matter? Below there's a screenshot from my sheet.



«1

Answers

  • David Tutwiler
    David Tutwiler Overachievers Alumni

    The easiest way to calculate this would be to convert your Start and Finish column types to Dates. If they are both dates, then you can use the simple formula in Duration that would be:

    =Finish@row - Start@row

    If you want that to calculate the same on every row of your sheet, right-click the formula in one of your rows and select Convert to Column Formula (the very last selection). This will lock the column and perform the same action on every row in the sheet.

  • OlliR
    OlliR ✭✭
    edited 10/05/22

    Thank you for you reply David. I guess my question was a bit badly formulated. I'd need to calculate the days between the earliest date in the whole "Start" column and the latest date in the whole "End" column to determine how many days overall there is during that time frame.


    In that example screenshot all those lines = projects would be handled by the same person, so his/her working time would be divided between all those projects. That's why I'd need to have the number of days so that I can then calculte the capacity and average load accordingly.



    Does this make any sense? :)

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭

    Hi @OlliR

    I hope you're well and safe!

    Try something like this.

    =(MAX(Finish:Finish) - MIN(Start:Start))

    Did that work/help?

    I hope that helps!

    Be safe, and have a fantastic week!

    Best,

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up, Awesome, or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

    SMARTSHEET EXPERT CONSULTANT & PARTNER

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

  • OlliR
    OlliR ✭✭

    Awesome, works like a charm 👍 Thank you so much Andrée 🙂

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭

    @OlliR

    Excellent!

    You're more than welcome!

    Remember! Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up/Awesome or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

    SMARTSHEET EXPERT CONSULTANT & PARTNER

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

  • OlliR
    OlliR ✭✭
    edited 10/06/22

    I just realised one more thing: How could I make the calculation so that it would only calculate dates that have value "Siivous" (2 possible options in a dropdown menu) on the same rows? In other words, it would ignore all the rows that have "PK" selected in the dropdown menu. Screenshot below:

    EDIT: As a more urgent question: It seems I don't know how to reference to a cell range with Date cells and MAX MIN. My formula looks like this and it only picks up the first row in question, row 3. Is this a known feature or my error in the formula (I presume the latter...):

    =MAX(Finish3:Finish12) - MIN(Start3:Start12)



  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭

    @OlliR

    Try something like this. (update the dropdown name to match yours)

    =MAX(COLLECT(FF:FF, Dropdown:Dropdown, "Siivous")) - MIN(COLLECT(SS:SS, Dropdown:Dropdown, "Siivous"))

    Did it work?

    Remember! Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up/Awesome or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

    SMARTSHEET EXPERT CONSULTANT & PARTNER

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

  • OlliR
    OlliR ✭✭

    Thank you for your reply Andrée 👍 Unfortunately I get #UNPARSEABLE error. Sorry, I'm pretty new to SmartSheet, so I don't grasp the FF:FF in the formula. What does it stand for? I replaced the Dropdown column name. The actual formula is below:

    =MAX(COLLECT(FF:FF, PK/Siivous:PK/Siivous, "Siivous")) - MIN(COLLECT(SS:SS, PK/Siivous:PK/Siivous, "Siivous"))

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭

    @OlliR

    I'm always happy to help!

    Apologies, I forgot to change the column name.

    Try this.

    =MAX(COLLECT(End:End, [PK/Siivous]:[PK/Siivous], "Siivous")) - MIN(COLLECT(Start:Start, [PK/Siivous]:[PK/Siivous], "Siivous"))

    Work?

    Remember! Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up/Awesome or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

    SMARTSHEET EXPERT CONSULTANT & PARTNER

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

  • OlliR
    OlliR ✭✭

    Thank you for a super fast reply Andrée 👍 Unfortunately the result is the same: #UNPARSEABLE error.

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭

    @OlliR

    Strange!

    I'd be happy to take a quick look.

    Can you maybe share some screenshots? (Delete/replace any confidential/sensitive information before sharing) That would make it easier to help. (share too, andree@workbold.com)

    SMARTSHEET EXPERT CONSULTANT & PARTNER

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

  • OlliR
    OlliR ✭✭


    Here's a screenshot about the sheet.

    Does this help at all?

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭

    @OlliR

    Yes, it does.

    The Finish Column name was wrong.

    Try this.

    =MAX(COLLECT(Finish:Finish, [PK/Siivous]:[PK/Siivous], "Siivous")) - MIN(COLLECT(Start:Start, [PK/Siivous]:[PK/Siivous], "Siivous"))

    Did it work?

    Remember! Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up/Awesome or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

    SMARTSHEET EXPERT CONSULTANT & PARTNER

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

  • OlliR
    OlliR ✭✭

    Thank you Andrée once again, now it runs. However there's still some weirdness related to the resulst, I try to explain.

    It seems to work great for the Siivous/PK "switch", e.g. the Siivous capacity (d) changes logically, when I select either Siivous or PK in the dropdown :) But If I change the Start or Finish date(s) it only seems to work for the first row with dates set. With other cells changing the dates does not result in any change in the Siivous capacity (d).

    I just tried by deleting all the dates except for the first row. The result of the days is correct. I then added one Start and Finish date on the row below with the Finish being 15 days longer ahead than the FInish date of the first row. The result was 3 days more?! So, there's something weird in how it actually calculates the days below the first row with dates.

    Hmm. This is a tough one for me to crack. Any clues Andrée? :)

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭

    @OlliR

    You're more than welcome!

    Do you want to show the actual days or only the working days?

    Can you maybe share the sheet(s)/copies of the sheet(s)? (Delete/replace any confidential/sensitive information before sharing) That would make it easier to help. (share too, andree@workbold.com)

    SMARTSHEET EXPERT CONSULTANT & PARTNER

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!