Counting the date range inside/between two columns?

OlliR

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.

David Tutwiler

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

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å

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!

OlliR

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

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!

OlliR

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å

@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!

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å

@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!

OlliR

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

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)

OlliR

Here's a screenshot about the sheet.

Does this help at all?

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!

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å

@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)