Seperating info in cells

[Marsh Family]

I have inherited an Excel spreadsheet where they are recording employees
times. Each employee has a column and each workday is represented in its
own cell. If they work, the number of hours is input in the cell. (e.g. 8)
If the person calls off sick it is recorded as S8 or vacation is V8,
representing the code and number of hours. Some code/time combinations have
more that 1 Alpha character such as BV8 for booked vacation. Right now the
totals of these are being calculated by a very long Countif statement where
they have =Countif(A2:A367,"VB8")*8+Countif(A2:A367,"VB7.50")*7.5+ .... so
that each instance is counted as 1 and multiplied by the number of hours to
calculate the total the number of hours for each code.
Is there an easier way to calculate the hours? I was thinking about using
an 'if' statement that would parse out the alpha characters and leave a
number that you coiuld use a Value on but have gotten bug eyed trying.
If anyone has an ideas or suggestion, it would be greatly appreciated.
David

 

[AlfD]

Hi!

I think you have inherited a data structure which is not of the best.
You can have combinations, as I understand it, of 1 or 2 letter
followed by a decimal number (V8; VB7.5; S7 etc).

Carrying two quantities (status, such as Sick; and hours, such as 7.5
in the same column makes life difficult, especially since the parts ar
or varying lengths.

Two things, therefore.

Historically, do you want to separate these parts so that the statu
letters go in one column and the numbers part goes in another? It woul
certainly make analysis easier and it can be done without too muc
effort (unless you have some more complex data structures than you hav
disclosed). Judicious use of sort and text to columns would handle it
for example.

Prospectively, do you intend/have to continue with this coding system


Al

 

[Aladin Akyurek]

If the letter set consists of a few letters, you could use...

=SUMPRODUCT(--SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(0&A2:A367,"V",""),"S",""),"B"
,""))