Sum question

[Guest]

I have an Excel document which has in the cell at the top of the column the
number of paid time off hours an employee gets each year (300). At the end
of each pay period I subtract out any time someone has taken off. For
instance if someone used 40 hours I put a "-40" in the corresponding cell.
At the bottom of the coulmn there is a cell that reads balance and it is
calculated by using =sum(C2:C32). I want to be able to have it included when
someone calls out sick. I want to be able to put "-8S" in the cell and have
the eight hours subtracted out. How do I get in to recognize when I put an
"S" in the cell?

Thanks much!

 

[Guest]

Sub isSick()
Selection.NumberFormat = "General""S"""
End Sub

make a shortcut to start makro


"edju" skrev:

 

[Guest]

I'm sorry I do not understand. I am new to this. I have checked older posts
but do not see any similar question. Right now to subtract out hours someone
was off I just place a "-10" in the cell if they took 10 hours off. I use
=sum() at the bottom of the coulmn in the "balance" row. I do not understand
what your suggestion is for me to try. I want it to recognize it if I put
"-10S" in a cell. S meaning sick abd it would subtract out 10 hours. Is
there another way you can explain it? Thanks greatly.

 

[Guest]

One more thing since people work diffrent hours per day the # of hours wgich
proceed the "S" would have to vary.

 

[Dave Peterson]

If you put those indicators in a different column (right next to the cell with
the value), you'll find that all your formulas will be easier to maintain.

 

[Guest]

Dave,

Thanks for the reply but I do not have enough space to add another coulmn
for each employee. I can barely fit all the employees or one sheet now.
That is why I want it all to be in the same coulmn. It would either just
have a 10 in the cell or a 10S. Isn't there a way to recognize when there is
an "S" next to the number?

 

[Guest]

well so in manuel way
put in fx. -8
then right click on this cell
custum format cell
General"S"
ok

the code i posted, do this automatic
to use it :
ATL+F11
pick Module in Insert menu
paste code in windows to the right

back in ur sheet
ALT+F8
shoose a letter right to CTRL (to start code)


"edju" skrev:

 

[Dave Peterson]

If it's just one cell (say C33), you could use:
=sum(c1:c32) + substitute(upper(c33),"S","")

If you could put it in any field:

=SUMPRODUCT(--SUBSTITUTE(UPPER(C1:C32),"S",""))

You may need a different formula if those cells could be empty.