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

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!

• ✭✭✭✭✭✭

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

• ✭✭✭✭✭✭
edited 08/30/24

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

• ✭✭✭✭✭✭
edited 08/30/24

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

• 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 😍.

• ✭✭✭✭✭✭

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

• ✭✭✭✭✭✭
edited 08/30/24

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

• ✭✭✭✭✭✭

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

• ✭✭✭✭✭✭

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

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

• ✭✭✭✭✭✭

Happy to help. 👍️

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!