Solved

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

Posted on 2002-03-09
6
1,382 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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
[Live Webinar] The Cloud Skills Gap

As Cloud technologies come of age, business leaders grapple with the impact it has on their team's skills and the gap associated with the use of a cloud platform.

Join experts from 451 Research and Concerto Cloud Services on July 27th where we will examine fact and fiction.

 
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

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

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

This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
Outlook for dependable use in a very small business   This article is about using the Outlook application (part of Microsoft Office) in a very small business, or for homeowners where dependability and reliability are critical requirements. This …
Learn how to create and modify your own paragraph styles in Microsoft Word. This can be helpful when wanting to make consistently referenced styles throughout a document or template.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

622 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