Q for Excel wizard - crunching estimates/actuals
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?
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
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.
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.