I could be nuts, but am trying to devise a simple 12-month on a page excel calendar on which I can record employee attendance using a drop-down menu (with conditional formatting) for various absences.

For example, S is for Sick Days, T is for Tardy, V is for vacation, and s is for .5 of a sick day, t is for .5 of a tardy day, v is for .5 of a vacation day, etc.

OK, got the drop-down calendar in hand, now - I want to be able to calculate the sum of: all S days (where S = 1, s = .5); T days (where T = 1, t = .5), etc., and calculate the total for the various months on a quarterly basis (i.e. 3 months across the page), and ultimately on a yearly basis (i.e. 4 rows of 3 months each).

The quarterly formulas are stumping me, big time!



It might also be a problem that the capital/lower case difference may not be recognizable in the formula (i.e. that it might not be possible to differentiate between a capital 'S' and a lower case 's', in which case - I'll have to come up with some other text for the half day record).

I THINK some kind of 'SUMIFS' formula is required, but I don't have enough experience to work it through.

Can anyone help me?

Thank you!

Attached Files

Attendance 2 (Recovered).xlsx (29.2 KB, 13 views)

What is the value of AA, B, S, V W, M etc.?

Try typing ="S"="s" in a cell. If you do that you'll see that excel doesn't differentiate between a capital or a lower case S.

In the attached I made a simple table that adds up the different instances for you. That you can then manipulate.

It's often easier to break down the problem into steps and then solve each step on it's own. Later you'll learn how to solve the problems more efficiently in fewer steps.

/Møller

Attached Files

Attendance 2 (Recovered).xlsx (31.3 KB, 23 views)

All are valued at 1; I also need to use a series of codes for .5 values.

The table you created is a great start for me, thank you. However - I still want to figure out a way to value the 1/2 day absences, and am looking for a formula that will add (for instance), all the V days (where V = 1) and all the vv days (where vv = .5) within a particular range of cells. (I understand now that I need a different kind of letter for the half days, and will need to change all the drop down formulas accordingly.)

Ideally - since I have (horizontally) - three months across, it would be something LIKE (obviously this is not even a pretend proper code.......):

= SUM (A5:G10) + (I5:O10) + (Q5:W10), only V=1 and vv=.5

Is this possible? I'm finding conflicting online information about assigning values to letters, but - it can't be THAT hard (can it???)!

Thank you!

Probably easier if you go Data base Access for this as you can expand greatly on it

As you don't seem interested I will delete my previous post. Good luck

__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post

If I was not interested I would not have given a suggestion I was only trying to point you in a different direction as data basing is where you should be going have a look it good stuff.

With apologies, if I didn't reply earlier - it's not that I'm not interested, but that I had only a few minutes to check in on this site and I probably didn't immediately understand your response. I believe you said something about pivot tables: I have limited experience with using pivots (i.e. I have never set up pivot tables, but have used spreadsheets where others have started them).

I am frustrated at my own lack of knowledge and am eager to learn.

Thanks for suggestion, Rah, but I'd like to stick with either Excel or Word. And I think Pecoflyer thought that it was I who wasn't interested, in his suggestion.

I'm new to this forum. Thanks!

Ok so am I but seriously you should go to Access as it is easier to extract and input info and can be more user friendly and is more what you require. Do some online training you wont regret it. Took me a number of years but I got really good at it even bought the book and with the help of good friend built a fantastic employee data base which recorded what tools they used for how long what machines they operated where they were now and where they were then endless

You need to identify how many of each instance there are of each type and then multiply with either 1 or 0.5. The sumproduct function can do that for you (https://exceljet.net/excel-functions...oduct-function), or you can augment the table that I created for you (see attached).

It's a bit difficult to understand what you are trying to achieve because if you just add up everything then the figure you get is essentially just days that aren't worked. I don't know how useful that is, because obviously a day on maternity leave isn't the same as a day where a person is just late. That beckons the question why would you then need to differentiate between the different types of non-working days.

I fully agree with the others who suggest a database or use of a pivot table, however if you are somewhat new to excel then those options would be goals that you have to work towards.

/Møller

Attached Files

Attendance 2 (Recovered) (1).xlsx (33.2 KB, 11 views)

Thank you, this helps enormously!