Solved

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

Posted on 2002-03-09
6
1,371 Views
Last Modified: 2007-12-19
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.
0
Comment
Question by:sandra_8309
6 Comments
 
LVL 4

Expert Comment

by:mousmasterbob
ID: 6852663
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
0
 
LVL 22

Accepted Solution

by:
ture earned 200 total points
ID: 6852785
sandra_8309,

With your 31 cells in A1:A31, this formula should do what you want:
=COUNTIF(A1:A31,"F")+COUNTIF(A1:A31,"H")*0.5

Ture Magnusson
Katlstad, Sweden
0
 
LVL 15

Expert Comment

by:dbase118
ID: 6854139
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
0
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
LVL 15

Expert Comment

by:dbase118
ID: 6854709
Whoops...middle part of formula would be absolute reference like $I$1:$J$4
0
 
LVL 13

Expert Comment

by:WJReid
ID: 6856170
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
0
 
LVL 22

Expert Comment

by:ture
ID: 6861375
sandra_8309,

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

/Ture
0

Featured Post

Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In case Office 2010 has not been deployed in your environment, this article may be quite useful. In our office, we wanted a way to deploy Microsoft Office Professional Plus 2010 through an automated batch file via logon script. This article is docum…
Microsoft Office Picture Manager is not included in Office 2013. This comes as a shock to users upgrading from earlier versions of Office, such as 2007 and 2010, where Picture Manager was included as a standard application. This article explains how…
This video shows the viewer how to set up and create Footnotes in their document. Click on the References tab: Select "Insert Footnote": Type in desired text:
This video shows where to find the word count, how to display it, and what it breaks down to in Microsoft Word.

773 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question