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' rollup. In a separate sheet, I am adding/cell linking all of these 'Day Needed' rollups.
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!
Best Answer

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

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

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

@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
Categories
 All Categories
 14 Welcome to the Community
 Smartsheet Customer Resources
 62.1K Get Help
 351 Global Discussions
 198 Industry Talk
 427 Announcements
 4.4K Ideas & Feature Requests
 133 Brandfolder
 127 Just for fun
 127 Community Job Board
 455 Show & Tell
 28 Member Spotlight
 1 SmartStories
 283 Events
 36 Webinars
 7.3K Forum Archives
Check out the Formula Handbook template!