Formula to remove the 'd' from "Days Needed" rollup number relinked into my summary Sheet?

JJLoganUniversity

Hello!

My company needs to determine how many Total/Average days it is taking our team to complete our projects. We use Smartsheet Project Schedules. And at the top of each project schedule is a 'Days Needed' roll-up. In a separate sheet, I am adding/cell linking all of these 'Day Needed' roll-ups.

In this separate sheet, I want to create a Helper column, or formula, so I can access the number WITHOUT the 'd' (90d, 125d, 10d, becomes 90, 125, 10, etc.) so I can use these day's numbers to determine totals/averages. Does anyone know a way to remove the pesky 'd' that is tacked on to indicate 'days'. Or a formula that will recognize and count the '#d' accurately?

For instance:

Column Range 1 (these are linked cells to my project schedule rollups)

121d

383d

303d

Formula in Summary Sheet:

=COUNTIF({Team Project Management Tracker Range 1}, <100d)

=COUNTIF({Team Project Management Tracker Range 1}, >100)

=AVG({Team Project Management Tracker Range 1})

None of these formulas work because the 'd' is not quantifiable and without the 'd' nothing in the column is recognized:

Help!

Paul Newcome

@Protonsponge Does it work if you use an AND to also include a condition of the cell not being blank?

=COUNTIFS({Range}, AND(@cell <> "", VALUE(SUBSTITUTE(@cell, "d", "")) < 100))

Protonsponge

Hello @JJLoganUniversity,

To go the helper column route, I think the formula below will give you what you need.

=LEFT([Days Needed]@row, LEN([Days Needed]@row) - 1)

All seems to be working in the demo below 😀

If you think you might have cells that are blank in you sheet, you can add in IFERROR statement in the formula to give a blank instead of the #INVALID VALUE.

=IFERROR(LEFT([Days Needed]@row, LEN([Days Needed]@row) - 1), " ")

I hope that is helpful to you in someway,

Protonsponge

Protonsponge

@Andrée Starå / @Paul Newcome - Is there a way to make this work with out the use of a helper column? I couldn't work out how to COUNTIF, the value in another sheet minus d, is <100 for example.

leo7422g

If you're working with a rollup field in your summary sheet that includes a "Days Needed" value with a 'd' suffix (like "5d" for 5 days), you might want to extract the number and remove the 'd' to use it for calculations or display purposes. To achieve this, you can create a formula that strips out the 'd' and leaves just the numeric value 😍.

Paul Newcome

@Protonsponge Even with the helper column, you are going to need to use a VALUE function. The LEFT function outputs a text string. I also suggest a SUBSTITUTE function instead of the LEFT. It requires one function instead of two which could help with processing power on larger sheets.

=VALUE(SUBSTITUTE([Column Name]@row, "d", ""))

I haven't tested it yet, but I think using an @cell reference in the SUBSTITUTE function should get you what you need without the helper column though.

=COUNTIFS({Range}, VALUE(SUBSTITUTE(@cell, "d", "")) < 100)

I know I have used the @cell reference with LEFT and VALUE functions before, so I don't see why it shouldn't work here.

Protonsponge

@JJLoganUniversity - My initial answer wasn't a good one and so please accept my apologies… but your question is great one and I learning lots here even though it not even my Question 😁 Luckily we have Paul to advise.

@Paul Newcome - I tried the =COUNTIFS({Range}, VALUE(SUBSTITUTE(@cell, "d", "")) < 100) and noted that it will count blank rows if the criteria is <100… the the criteria is >100 it work perfectly. What would you advise as the best work around for that?

Paul Newcome

@Protonsponge Does it work if you use an AND to also include a condition of the cell not being blank?

=COUNTIFS({Range}, AND(@cell <> "", VALUE(SUBSTITUTE(@cell, "d", "")) < 100))

Protonsponge

@Paul Newcome - works like a dream!

@JJLoganUniversity - You've Paul to thank for a solid solution without the need for a helper column, I hope it works for your solution. Great Q!

Have a fantastic weekend!

JJLoganUniversity

Thank you everyone for all of your time and support. This works perfectly. What amazing teamwork and brainstorming. So appreciate it, Paul and everyone.

Paul Newcome

Happy to help. 👍️