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

Posted on 2002-03-09
Medium Priority
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?

Question by:sandra_8309

Expert Comment

ID: 6852663

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


LVL 22

Accepted Solution

ture earned 800 total points
ID: 6852785

With your 31 cells in A1:A31, this formula should do what you want:

Ture Magnusson
Katlstad, Sweden
LVL 15

Expert Comment

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
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

LVL 15

Expert Comment

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

Expert Comment

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
LVL 22

Expert Comment

ID: 6861375

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


Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

This is an article on how to answer questions, earn points and become an expert.
In this post, we will learn to set up the Group Naming policy and will see how it is going to impact the Display Name and the Email addresses of the Group.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
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.

597 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