Solved

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

Posted on 2002-03-09
6
1,364 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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Introduction Perhaps more familiar to developers who primarily use VBScript than to developers who tend to work only with Microsoft Office and Visual Basic for Applications (VBA), the Dictionary is a powerful and versatile class, and is useful …
Entering a date in Microsoft Access can be tricky. A typo can cause month and day to be shuffled, entering the day only causes an error, as does entering, say, day 31 in June. This article shows how an inputmask supported by code can help the user a…
This video walks the viewer through the process of creating Hyperlinks for the web and other documents. Select the "Insert" tab: Click "Hyperlink":  Type "http://" followed by a web address to reference a website or navigate to a document to ref…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

744 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now