It's all about the answers!

Ask a question

Q for Excel wizard - crunching estimates/actuals

Virginia Brown (9333941) | asked May 24 '12, 9:13 a.m.
We have estimates set to hours so an export of estimates and time spent
come through with an hour suffix:

Item Summary Estimate Time Spent
43333 Eat More Spinach campaign 10 hours 4 hours

What trickery is available to convert Estimate and Time Spent to numeric fields?
I tried forcing a format (#" hours") but that seems to only work for new entries

Is a VBScript macro needed here?

One answer

permanent link
Virginia Brown (9333941) | answered Jul 25 '12, 11:18 a.m.
edited Aug 08 '12, 2:23 p.m.
Here is an excel transformation trick that will work, though I still suspect there is a simpler solution.
In particular we're interesting in doing some number crunching on estimates v actuals. '

Note: we are forcing estimates and actuals to hours so we only need to deal with
transforming '10 hours' char to 10 numeric

              =VALUE(TRIM(CLEAN(SUBSTITUTE(F2," hours",""))))

The substitute is obvious (thow out the hours postfix),
'trim(clean...' appears to be needed for Excel to recognize the result as a numeric.

Your answer

Register or to post your answer.

Dashboards and work items are no longer publicly available, so some links may be invalid. We now provide similar information through other means. Learn more here.