asked on

working attendance table on a spreadsheet (ms excel 97/2000)

I’m trying to create a working attendance table on a spreadsheet (ms excel 97/2000).
----------------------------------------------------
F: 1 day
H: 0.5 day
A: 0 day
S: 0 day

F: full day, H: half day, A: Absence, S: Sick

One table represent a worker attendance that have 31 cells which means 31 days. So if I enter F in a cell and it will give 1 day value, H will give 0.5 day, A and S will give 0 day value. Then, there will be a cell to show total of working days for a worker.
---------------------------------------------------------
Can someone show how to solve the above problem?

Thanks.

mousmasterbob

sandra

I set up table in I1,I2,I3,I4 were I1=1,I2=.05 and so on
days are in column 1 letters are typed in column 2 formula is in column 3 copy this down

=IF(B2="f",$I$1,IF(B2="h",$I$2,IF(B2="a",$I$3,IF(B2="s",$I$4))))

...Bob

ASKER CERTIFIED SOLUTION

ture

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

dbase118

I used a setup similar to mousemaster but used a VLookup formula in column three like

=VLOOKUP (B2,I1:J4,2) then copy it all the way down

dbase118

Whoops...middle part of formula would be absolute reference like $I$1:$J$4

WJReid

With your 31 days in cells a1:a31 and your f,h,a and s to be filled in cells B1:b31. The formula in cell c1 should be =(b1="h")*0.5+(b1="f"). Copy this formula to cells C2:C31.
The formula in Cell C32 should be as suggested from Ture

ture

sandra_8309,

It's been a while... Did any of our suggestions help you?

/Ture